Mysql count, return all fields, if nothing counted return 0

Question

I've been looking around for alot of people having the same problem, but the solution seems te be much more different?!

I have a 3 tables:

+-------------------------------------------+
| ITEM: itemid(pk), status(fk), owner(fk)   |
|                                           |
| STATUS: statusid(pk), statusname          |
|                                           |
| OWNER: ownerid(pk), ownername             |
+-------------------------------------------+

I have 2 status: 1: Available, 2: Broken.

In this case there is only ONE owner with ONE broken item. I am using this code. First it was different, but it seems LEFT JOIN and GROUP/ORDER BY should do the trick. Unfortunately for me.

SELECT ownername, SUM(price) AS  'totalbrokenpriceeach', COUNT( itemid ) AS  'totalbrokenitemseach'
                                FROM item
                                LEFT JOIN owner ON item.owner = owner.ownerid
                                LEFT JOIN status on item.status = status.statusid
                                WHERE statusid='2'
                                GROUP BY ownerid

This returns:

ownername   totalbrokenpriceeach    totalbrokenitemseach
Owner #1            60                          1
Owner #5            180                         4

I'd like to have returned:

ownername   totalbrokenpriceeach    totalbrokenitemseach
Owner #1            60                          1
Owner #2            0                           0
Owner #3            0                           0
Owner #4            0                           0
Owner #5            180                         4

What to do? Anyone any solutions?

EDIT:

         OWNER                          
+-----------------------+
| ownerid ownername     |
|     1:    Henk        |
|     2:    Jan         |
|     3:    Piet        |
|     4:    Klaas       |
+-----------------------+

         STATUS
+-----------------------+
| statusid statusbeschr |
|     1:    Available   |
|     2:    Broken      |
+-----------------------+

         ITEM
+--------------------------------------+
| itemid    price     owner     status |
|     1:    90          1          1   |
|     2:    40          2          1   |
|     3:    20          2          1   |
|     4:    120         3          2   |
+--------------------------------------+

I need returned:

+-------------------------------------------------------+
| ownername    SumOfBrokenItems   AmountOfBrokenItems   |
|     Henk:       0                       0             |
|     Jan:        0                       0             |
|     Piet:       120                     1             |
|     Klaas:      0                       0             |
+-------------------------------------------------------+

I tried, right, right outer, left, left outer, left inner, even combinating the two untill sql started to whine :P — TMNuclear
– TMNuclear, Commented Nov 7, 2012 at 14:57
My bad, I thought the status was a column from owner, not item. — Vincent Savard
– Vincent Savard, Commented Nov 7, 2012 at 15:03

John Woo · Accepted Answer · 2012-11-07 15:36:54Z

3

SELECT a.ownername, SUM(price) AS totalbrokenpriceeach, COUNT(b.itemid) AS totalbrokenitemseach FROM owner a LEFT JOIN item b ON a.ownerID = b.owner LEFT JOIN status c ON b.status = c.statusID AND c.StatusID = 2 GROUP BY ownername

UPDATE 1

SELECT  a.ownername, 
        SUM(CASE WHEN c.statusbeschr = 'Broken' THEN b.price ELSE 0 END) AS  totalbrokenpriceeach, 
        SUM(CASE WHEN c.statusbeschr = 'Broken' THEN 1 ELSE 0 END) AS  totalbrokenitemseach
FROM    owner a
        LEFT JOIN   item b
            ON a.ownerID = b.owner
        LEFT JOIN   status c
            ON b.status = c.statusID        
GROUP BY a.ownerid
ORDER BY a.ownerid

SQLFiddle Demo

edited Nov 7, 2012 at 15:36

answered Nov 7, 2012 at 15:00

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

TMNuclear Over a year ago

This counts all fields together with the value. Only need that for status 2, and return 0 with the owner if status = 2 is not found.

John Woo Over a year ago

@IvanM can you add sample records on your question?

John Woo Over a year ago

did you see the fiddle right? it's the same as what you wanted.

TMNuclear Over a year ago

OHH MY bad! I see. I wrote BROKEN, it supposed to be in another language! Thanks aloT =D=D+D+D

John Woo Over a year ago

maybe it has to do with the records on your table. hehe weird.

|

Collectives™ on Stack Overflow

Mysql count, return all fields, if nothing counted return 0

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related