CODEWITHSUNDEEP
mysql
Gerardo Abdo
Gerardo Abdo

Reputation: 1210

Mysql query group by including a count column

I have a query that result as big table with more than 30 columns, I summary the situation on this table. The result is as follows:

Rte_Id  Qty1    Mist
5       15      195
5       8       94
20      10      64
20      6       158
23      18      0
23      5       0
23      12      1
23      2       100
23      0       81
23      6       0

I have a query that need to modify and honestly not sure if possible

select rte_id as a, sum(qty1) as b as c from table1 group by rte_id

I want to add a third value in query and do not know if possible. The idea is to display the times "mist" has value lower than 2.
My desired result is: 

a   b   c
5   23  0
20  16  0
23  43  4

Do you know a simple way to correct this mysql script.

Upvotes: 1

Views: 17

Answers (1)

sgeddes
sgeddes

Reputation: 62861

You can use conditional aggregation for this:

select rte_id as a, 
    sum(qty1) as b,
    sum(case when mist < 2 then 1 else 0 end) as c 
from table1 
group by rte_id

Since you're using mysql, you could also just use:

    sum(mist < 2) as c

Upvotes: 1

Related Questions