CODEWITHSUNDEEP
mysql
afraid.jpg
afraid.jpg

Reputation: 1175

How to count in a range of result in mysql

l have a record table now, and l must to statistics the result of every month.

here is a test table

+----+------+----------+----------+------+
| id | name | grade1   | grade2   | time |
+----+------+----------+----------+------+
|  1 | a    | 1        | 1        |    1 |
|  2 | a    | 0        | 1        |    1 |
|  3 | a    | 1        | 2        |    2 |
|  4 | b    | 1        | 2        |    2 |
|  5 | a    | 1        | 1        |    2 |
+----+------+----------+----------+------+
5 rows in set (0.01 sec)

time column means month(the actual is timestamp).

l need to statistics total number those grade1 >=1 && grade2 >=1 in every month

So, l want to get the result like this

+----+------+----------+----------+----------+----------+------+
| id | name | grade1_m1| grade2_m1| grade1_m2| grade2_m2| time |
+----+------+----------+----------+----------+----------+------+
| 13 | a    | 1        | 2        | null     | null     |    1 |
| 14 | a    | null     | null     | 2        | 2        |    2 |
| 15 | b    | null     | null     | 1        | 1        |    2 |
+----+------+----------+----------+----------+----------+------+
3 rows in set (0.00 sec)

fake code of sql seem like this:

select
    count(grade1 where time=1 and grade1 >= 1) as grade1_m1,
    count(grade2 where time=1 and grade2 >= 1) as grade1_m1,
    count(grade1 where time=2 and grade1 >= 1) as grade1_m2,
    count(grade2 where time=2 and grade2 >= 1) as grade1_m2,
    -- ... 12 months' statistics
from test
    group by name

In the fact, l done it, but with temporary table like follow:

select
    count(if(m1.grade1>=1, 1, null)) as grade1_m1,
    count(if(m1.grade2>=1, 1, null)) as grade2_m1,
    count(if(m2.grade1>=1, 1, null)) as grade1_m2,
    count(if(m2.grade2>=1, 1, null)) as grade2_m2,
    -- ... 
from test
left join
    (select * from test where time = 1) as m1
on m1.id = test.id
left join
    (select * from test where time = 1) as m2
on m2.id = test.id
-- ...
group by name

But this sql is toooooooo long. this test table is just a simple version. Under real situation, l printed my sql and that took up two screens in chrome. So l am seeking a more simple way to complete it

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You're original version is almost there. You need case and sum() is more appropriate:

select name,
       sum(case when time=1 and grade1 >= 1 then grade1 end) as grade1_m1,
       sum(case when time=1 and grade2 >= 1 then grade2 end) as grade2_m1,
       sum(case when time=2 and grade1 >= 1 then grade1 end) as grade1_m2,
       sum(case time=2 and grade2 >= 1 then grade2 end) as grade2_m2,
    -- ... 12 months' statistics
from test
group by name

Upvotes: 1

Related Questions