SQL - Difficult query - SUM multiple rows to columns

Question

I have a little problem with the query I'm trying to develop.

Here's what my table looks like: -

Account Table

ClientNo AccountType Balance 1234 SUP1 25 1234 SUP1.1 35 1234 RET1 20 1111 SUP1 50 1111 DIS4 60 

I am trying to get a result that looks like this: -

 ClientNo TotSupBal TotSuppAccts TotRetBal TotRetAccts TotDisBal TotDisAccts 1234 70 2 20 1 0 0 1111 50 1 0 0 60 1 

Essentially, a client can be in the account table many times, since there can be many accounts for each client.

Account types always start with the same characters to begin with, but depending on how many of these accounts the number can be something really, and subsequent accounts will always be decamenal, and then the number ... for example. the first SUP account is just SUP1, however the next SUP account will be SUP1.1, then SUP1.2, etc ...

I wrote the following query

 SELECT ClientNo, SUM(Balance) AS TotSupBal, COUNT(AccountType) AS TotSuppAccts FROM Account WHERE (AccountType LIKE 'SUP1.%') OR (AccountType = 'SUP1') GROUP BY ClientNo 

* The reason is that there are 2 different WHERE clauses, because I cannot just use SUP1%, since there are accounts like SUP12 that do not match SUP1.

This query works fine, however, it only lists the SUP account type. How can I create the same output, however in multiple columns for each type of account?

I am using Microsoft SQL 2008 R2

Answer 1

PIVOT is what you need -> http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Here is a fully working solution:

 WITH Accounts (AccountCategory, ClientNo, Balance) as ( select case when AccountType like 'SUP%' then 'sup' when AccountType like 'RET%' then 'ret' when AccountType like 'DIS%' then 'dis' end as AccountCategory, ClientNo, Balance from Account ) select * from ( select ClientNo, sup as TotSupBal, ret as TotRetBal, dis as TotDisBal from Accounts as SourceTable PIVOT ( SUM(Balance) FOR AccountCategory IN ([sup], [ret], [dis]) ) as pt ) as sums inner join ( select ClientNo, sup as TotSupAccts, ret as TotRetAccts, dis as TotDisAccts from Accounts as SourceTable PIVOT ( COUNT(Balance) FOR AccountCategory IN ([sup], [ret], [dis]) ) as pt ) as counts on sums.ClientNo = counts.ClientNo 

Try it on SqlFiddle: http://sqlfiddle.com/#!6/d5e91/26

Answer 2

Let me assume that you know what types of accounts are in advance. In this case, you just need the amount of conditional aggregation:

 select clientNo, sum(case when (AccountType LIKE 'SUP1.%') OR (AccountType = 'SUP1') then Balance end) as TotSupBal, sum(case when (AccountType LIKE 'SUP1.%') OR (AccountType = 'SUP1') then 1 else 0 end) as TotSupAccts, sum(case when left(AccountType, 3) = 'RET' then Balance end) as TotRetBal, sum(case when left(AccountType, 3) = 'RET' then 1 else 0 end) as TotRetAccts, . . . from account group by clientNo 

I'm not sure what the exact logic is for other accounts, so I'm just looking at the first three characters.

Answer 3

 SELECT ClientNo, SUM(Balance) AS TotSupBal, COUNT(AccountType) AS TotSuppAccts, ret_bal AS TotRetBal, total_ret AS TotRetAccts FROM Account, ( SELECT ClientNo c_num, SUM(Balance) AS ret_bal, COUNT(AccountType) total_ret WHERE AccountType LIKE 'RET%' GROUP BY ClientNo ) Table1RET_type -- Your name for new table(You create new temporary table for you select) WHERE ((AccountType LIKE 'SUP1.%') OR (AccountType = 'SUP1')) AND Table1RET_type.c_num = ClientNo -- This is called join Table(google it for more info) GROUP BY ClientNo 

Now you need to repeat this logic for all the columns that you want to create.