mysql group by query

Question

I have the following table:

Service_ID   feedback  
31             1
32             1
33             1 

I have the following query to find the sum:

SELECT Service_ID,SUM(consumer_feedback) FROM consumer5 group by Service_ID

I have to get the output as

31 1
32 1
33 1

I am getting it. But for my project I have 34, 35 also. I should get output as

31 1
32 1
33 1
34 0
35 0

Answer 1

For this you will need to outer join on a table of all service IDs. It will be something like this, though I am sure this syntax is a little bit wrong:

SELECT Service_ID, total FROM (SELECT Service_ID,SUM(consumer_feedback) AS total FROM consumer5 group by Service_ID) OUTER JOIN service_ids ON Service_ID

Answer 2

Please post the table information (structure query), so we can tell you more. Also please post the data as well where the id 34 and 35 appears.

if you have the data like in the following example then you will get the results for 34 and 35 as well:

31     1
32     1
33     1
34     0
35     0

If you have it like this, then you will get the result for 34 and 35 as well, but you will get NULL as sum:

31     1
32     1
33     1
34     null
35     null

Answer 3

you may also be interested in reading about JOINS:
http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

Answer 4

SELECT service_id,
       SUM(IFNULL(feedback, 0))
FROM service_table
    LEFT JOIN feedback_table USING (service_id)
GROUP BY service_id;