CODEWITHSUNDEEP
mysqlselectcount
user1597915
user1597915

Reputation: 41

Display Zero Count in MySQL query

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.

Upvotes: 0

Views: 151

Answers (1)

podiluska
podiluska

Reputation: 51494

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

Upvotes: 1

Related Questions