Counting Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Counting Question

Author

Topic

milczy
Starting Member

2 Posts

Posted - 2011-03-06 : 20:05:36

I am having trouble with a counting issue. It's probably an easy solution, but I am just having a mind blank.

Here is my data
table name #transactions

Customer# TransactionType
1 FOOD
1 BEVERAGE
1 FOOD
1 FOOD
1 BEVERAGE
2 BEVERAGE
2 BEVERAGE
3 FOOD
3 FOOD
3 FOOD
3 FOOD

So now I want to do some counting.

I am fine with the total number of customers and transactions

SELECT COUNT(DISTINCT Customer#) TotCustomers,
COUNT(Customer#) TotTransactions
COUNT(DISTINCT Customer#) ???? (filter on beverage only) BevCustomers,
COUNT(Customer#) ?????? (filter on beverage only) BevTransactions,
COUNT(DISTINCT Customer#) ???? (filter on food only) FoodCustomers,
COUNT(Customer#) ?????? (filter on food only) FoodTransactions,
FROM #transactions

In the same statement I want to see how many customers made beverage transactions and how many beverage transactions there were in total and then how many made food transactions and how many food transactions there were along with the total customers and total transactions!

The results would look like

TotCustomers TotTransactions BevCustomers BevTransactions FoodCustomers FoodTransactions
3 11 2 4 2 7

Thanks in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-06 : 20:26:32

[code]
SELECT COUNT(DISTINCT Customer#) TotCustomers,
COUNT(Customer#) TotTransactions
COUNT(DISTINCT case when TransactionType = 'BEVERAGE' then Customer# end) BevCustomers,
COUNT(case when TransactionType = 'BEVERAGE' then Customer# end) BevTransactions,
. . .
FROM #transactions
[/code]

KH
[spoiler]Time is always against us[/spoiler]

milczy
Starting Member

2 Posts

Posted - 2011-03-06 : 21:18:39

Thanks for that. I have used the case to get my beverage transactions, but how do I incorporate the DISTINCT on customer# to get the customers who made beverage transactions. It's not liking when I put in the distinct.

COUNT(CASE WHEN TransactionType = 'BEVERAGE' THEN DISTINCT Customer# END) BevCustomers
Doesn't like the syntax here.

Thanks again in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-06 : 21:23:08

[code]
COUNT( DISTINCT CASE WHEN TransactionType = 'BEVERAGE' then Customer# END) BevCustomers
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources