CODEWITHSUNDEEP
sql
GhostBen
GhostBen

Reputation: 11

Using Group By With "Select" Subqueries

I'm trying to group my table by weekday, so I can see the count of each payment type along with other sales information split out by weekday. However, because I'm using subqueries, I just get a list of repeating values since the subquery can only return a single value. Is there an easy way to group this by weekday? I think I read elsewhere that I might have to do a series of joins for each subquery, but since there are 8 of them, I'd rather not do that. My current code is below:

select datename(weekday, transaction_date) as [Day],
(select count(tender_type) from store_visits where tender_type = '0') as [Cash],
(select count(tender_type) from store_visits where tender_type = '1') as [Check],
(select count(tender_type) from store_visits where tender_type = '2') as [Gift Card],
(select count(tender_type) from store_visits where tender_type = '3') as [Discover],
(select count(tender_type) from store_visits where tender_type = '4') as [Direct Credit],
(select count(tender_type) from store_visits where tender_type = '5') as [Business Credit],
(select count(tender_type) from store_visits where tender_type = '6') as [Personal Credit],
(select count(tender_type) from store_visits where tender_type = '7' or tender_type = '8' or tender_type = '9') as [Other] 
from store_visits group by datename(weekday, transaction_date)

Upvotes: 1

Views: 44

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This is called conditional aggregation. Use a case expression in count.

select datename(weekday, transaction_date) as [Day],
count(case when tender_type = '0' then 1 end) as [Cash],
count(case when tender_type = '1' then 1 end) as [Check],
count(case when tender_type = '2' then 1 end) as [Gift Card],
count(case when tender_type = '3' then 1 end) as [Discover],
count(case when tender_type = '4' then 1 end) as [Direct Credit],
count(case when tender_type = '5' then 1 end) as [Business Credit],
count(case when tender_type = '6' then 1 end) as [Personal Credit],
count(case when tender_type in ('7','8','9') then 1 end) as [Other] 
from store_visits
group by datename(weekday, transaction_date)

Upvotes: 1

Related Questions