CODEWITHSUNDEEP
mysqlsql
Lengo
Lengo

Reputation: 342

mysql query group and show possible values

I'm looking for an awser, but can't find it on internet (maby because I don't know the correct term). I'm in search of a query that groups my values and also shows zero's for predefined values.

My table has values from 0 - 5 (example 1,0,4,4,0,0,4,2,1,5,0)

I need a query that always shows al the numbers (also with a zero value):

nr | amount
0 | 4
1 | 2
2 | 1
3 | 0
4 | 3
5 | 1

Is this possible?

Greets, Len

Upvotes: 2

Views: 112

Answers (2)

Tim3880
Tim3880

Reputation: 2583

Create a numbers table with single column "num" and insert all your numbers (0-5).

Then do a left join query and group by the num like this:

SELECT num, SUM(amount) AS total
FROM amounts LEFT JOIN numbers ON amounts.nr=numbers.num
GROUP BY num;

Upvotes: 0

peter.petrov
peter.petrov

Reputation: 39457

Here is what you need. This assumes that you know up-front that the values 0-5 are the only possible values that can be present in your table. If other values are present, that's OK but you won't count them.

select m.nr, ifnull(m.cnt,0) as amount

from 
(
    select * from 
    (
        select 0 as nr
        union all
        select 1 
        union all
        select 2
        union all
        select 3
        union all
        select 4
        union all
        select 5
    ) t1 

    left join 

    (
        select v, count(*) as cnt from
        test
        group by v

    ) t2 on t1.nr = t2.v 

) m;

SQL Fiddle here:

http://sqlfiddle.com/#!9/bc704f/12

Upvotes: 2

Related Questions