SQL GROUP BY with FILTER IN SELECT
If I have the following sample data:
╔══════════════╦══════════════════╦════════════╦═══════╗
║ Client ║ con_id ║ mat1_07_03 ║ Ccode ║
╠══════════════╬══════════════════╬════════════╬═══════╣
║ Clients Name ║ C13109BBFD511534 ║ $1,062.00 ║ NOFL ║
║ Clients Name ║ C11AC9BBF74D6882 ║ $879.73 ║ NOFL ║
║ Clients Name ║ C12A69BBF1ACB578 ║ $2,790.29 ║ NOFA ║
║ Clients Name ║ C12A69BBF1ACB578 ║ $912.00 ║ NOFL ║
║ Clients Name ║ C6B0CA1A767C9744 ║ $2,180.11 ║ NOFL ║
║ Clients Name ║ C11AC9BBF74D6882 ║ $878.67 ║ NOFA ║
║ Clients Name ║ C13B79BBF4F1F450 ║ $300.00 ║ NOFL ║
║ Clients Name ║ C12A69BBF1ACB578 ║ $1,790.67 ║ NOFL ║
║ Clients Name ║ CA6869E2FE38A449 ║ $240.00 ║ NOFA ║
║ Clients Name ║ C46439FB0D847140 ║ $3,392.66 ║ NOFL ║
║ Clients Name ║ C12A69BBF1ACB578 ║ $1,791.73 ║ NOFA ║
║ Clients Name ║ C13B49BBF12ED236 ║ $0.00 ║ NOFL ║
║ Clients Name ║ C12A69BBF1ACB578 ║ $879.73 ║ NOFL ║
╚══════════════╩══════════════════╩════════════╩═══════╝
And Apply the following query:
SELECT
[Client]=MAX(m.Client)
,[CaseCount]=COUNT(m.con_id)
,[AmtInDispute]=CONVERT(char, SUM(Convert(money, m.mat1_07_03)), 101)
FROM lntmu11.matter m
GROUP BY m.con_id
ORDER BY COUNT(m.Client) DESC
How can I further group the column Ccode
to get COUNT
how many rows
NOFA
orNOFL
My desired output appears as:
╔══════════╦═══════════╦═══════════════╦═══════════════╦══════════════╗
║ Client ║ CaseCount ║ NOFACaseTotal ║ NOFLCaseTotal ║ AmtInDispute ║
╠══════════╬═══════════╬═══════════════╬═══════════════╬══════════════╣
║ Client A ║ 3548 ║ 2000 ║ 1548 ║ 5,658,307.60 ║
║ Client B ║ 3366 ║ 100 ║ 3266 ║ 2,885,649.48 ║
║ Client C ║ 3014 ║ 800 ║ 2214 ║ 2,851,507.13 ║
║ Client D ║ 2340 ║ 340 ║ 2000 ║ 3,467,207.12 ║
╚══════════╩═══════════╩═══════════════╩═══════════════╩══════════════╝
source to share
Just use conditional aggregation:
SELECT m.Client, CaseCount = COUNT(m.con_id),
CONVERT(varchar(255), SUM(Convert(money, m.mat1_07_03)), 101) as AmtInDispute
sum(case when cCode = 'NOFA' then 1 else 0 end) as NOFACaseTotal,
sum(case when cCode = 'NOFL' then 1 else 0 end) as NOFLCaseTotal,
FROM lntmu11.matter m
GROUP BY m.client
ORDER BY COUNT(m.Client) DESC;
Note: When doing conversions to a character, always include the length (in T-SQL).
source to share
You can use conditional aggregation to get the other two columns. Using an aggregate function with a CASE expression to count the values Ccode
you want for each of the columns.
SELECT
[Client]=MAX(m.Client)
,[CaseCount]=COUNT(m.con_id)
,NOFACaseTotal = sum(case when m.Ccode = 'NOFA' then 1 else 0 end)
,NOFLCaseTotal = sum(case when m.Ccode = 'NOFL' then 1 else 0 end)
,[AmtInDispute]=CONVERT(char(50), SUM(Convert(money, m.mat1_07_03)), 101)
FROM lntmu11.matter m
GROUP BY m.con_id
ORDER BY COUNT(m.Client) DESC;
See SQL Fiddle with Demo .
source to share
Like Gordon and Blufett said FTW's Conditional Aggregation!
SELECT
[Client]=MAX(m.Client)
,[CaseCount]=COUNT(m.con_id)
,[AmtInDispute]=CONVERT(char, SUM(Convert(money, m.mat1_07_03)), 101)
,[NOFACaseTotal]=sum(case when Ccode = 'NOFA' then 1 else 0 end)
,[NOFLCaseTotal]=sum(case when Ccode = 'NOFL' then 1 else 0 end)
FROM lntmu11.matter m
GROUP BY m.con_id
ORDER BY COUNT(m.Client) DESC
source to share