MySQL Query for count of same column separately based on condition

Question

We have an order table with fields as below e.g.

Timestamp PaymentID OrderID
341231231 6           1
342131231 12          2
123123123 18          3
123123122 14          4
123123143 12          5
433453454 6           6
445456456 18          7

What we want to do is get an output which will give us a month wise report on order count for each Payment Type but the payments are to be clubbed together for e.g. 6,8 PaymentID comes under type C so the count should be added for both in one and all other PaymentID's come under type P

So the output what we want is like below.

Year   Month   C_Orders P_Orders
2015   01       0        4
2015   02       4        3
2015   03       1        0
2015   04       2        1

We tried 2 queries but has incorrect outputs

  select SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+5:30'),1,4) as year,SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+5:30'),6,2) as month, co.payment_id, count(co.payment_id) as c_orders,co1.payment_id, count(co1.payment_id) as p_orders from
orders as co, orders as co1
WHERE  co.payment_id in (6,18)
AND co1.payment_id not in (6,18)
GROUP BY year,month

AND

select SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+5:30'),1,4) as year,SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+5:30'),6,2) as month, 'COD', count(co.payment_id) as cod_orders
from
orders as co
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2014-01-01 00:00:00','+00:00','+5:30')) AND co.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2020-12-31 23:59:59','+00:00','+5:30')) AND co.is_parent_order = 'N' AND co.status IN ('C','G','E','P') AND co.payment_id in (6,18)
GROUP BY year,month
union 
select SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+5:30'),1,4) as year,SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+5:30'),6,2) as month, 'PREPAID', count(co.payment_id) as prepaid_orders
from
orders as co
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2014-01-01 00:00:00','+00:00','+5:30')) AND co.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2020-12-31 23:59:59','+00:00','+5:30')) AND co.is_parent_order = 'N' AND co.status IN ('C','G','E','P') AND co.payment_id not in (6,18)
GROUP BY year,month

Can you also post the table with which you obtain values for the C_Orders and P_Orders columns? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 8, 2015 at 9:12

AdrianBR · Accepted Answer · 2015-09-08 09:21:52Z

1

Use a case statement to only sum/count the values when a condition is met.

select year, month,
sum(case when payment_id in (6,18) then 1 else 0 end) as 'payment_id in (6,18)',
count(case when payment_id not in (6,18) then payment_id else null end) as 'payment_id not in (6,18)'
from table
group by 1,2

answered Sep 8, 2015 at 9:21

AdrianBR

2,5781 gold badge17 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

MySQL Query for count of same column separately based on condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related