CODEWITHSUNDEEP
mysqlgroup-by
amit
amit

Reputation: 1

MySQL Query for count of same column separately based on condition

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

Upvotes: 0

Views: 88

Answers (1)

AdrianBR
AdrianBR

Reputation: 2588

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

Upvotes: 1

Related Questions