I have a table in my database named contacts and a table named views.
On the table contacts I have the following fields:
- id
- status
- first_name
- last_name
The status can be cold, prospect or lost.
On the table views I have the following fields:
- user_id
- art_views
- art_title
The relation between those 2 tables is id and user_id.
I need a query to make a new html table with the following columns:
- art_title
- cold
- prospect
- lost
Now I have the following query (UPDATED):
SELECT
v.art_title,
SUM(CASE c.status WHEN 'cold' THEN v.art_views ELSE 0 END) cold,
SUM(CASE c.status WHEN 'prospect' THEN v.art_views ELSE 0 END) prospect,
SUM(CASE c.status WHEN 'lost' THEN v.art_views ELSE 0 END) lost
FROM views v
JOIN contacts c ON v.user_id = c.id
GROUP BY v.art_title
This query is working now (thanks to Gerv) but i still have users who don't have a status. So i leave the field user_id in the table 'views' empty. How can i change the query for those users so i can count them also?
I tried to: SUM(CASE v.user_id WHEN ' ' THEN v.art_views ELSE 0 END) test, but with no result here.
