Display Zero Count in MySQL query

Question

i have a table in my database named subscriber which contains two rows one for the subscriber id and another one for points earned by each subscriber . My Select query :

SELECT 1000 * ( LOYALTY_POINTS DIV 1000 ) AS  'from', 1000 * ( LOYALTY_POINTS DIV 1000 ) +1000 AS  'to', COUNT( * ) AS NUMBER FROM SUBSCRIBER GROUP BY LOYALTY_POINTS DIV 1000

should return for each range of points the number of subscribers but unfortunately it only returns the number of subscribers different than zero. My result is:

from to          NUMBER
0       1000     8
1000 2000     2
3000 4000     1

I want the query to return records with zero coun also. The result should be:

from to          NUMBER
0       1000     8
1000 2000     2
2000 3000     0
3000 4000     1

How to do it? Thank you in advance.

podiluska · Accepted Answer · 2012-09-02 08:12:18Z

1

You need to generate some numbers to indicate your range

 SELECT 0 as RangeStart UNION Select 1000 UNION Select 2000 UNION Select 3000

and LEFT JOIN this to your results.

SELECT 
    RangeStart, RangeStart + 999 as RangeEnd, RangeCount
FROM
    (SELECT 0 as RangeStart UNION Select 1000 UNION Select 2000 UNION Select 3000) range
         LEFT JOIN
    (Select LoyaltyPoints DIV 1000 as loyaltypoints, count(*) as RangeCount group by LoyaltyPoints DIV 1000 ) counts
    ON range.rangestart = counts.loyaltypoints

answered Sep 2, 2012 at 8:12

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

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

1 Comment

eggyal Over a year ago

Agreed +1. But I'd add that it's often easier to simply treat a missing range as zero from within one's application code.

Collectives™ on Stack Overflow

Display Zero Count in MySQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related