August 11, 2012 at 9:27 am
SQL Server - GROUP BY clause
Hi there, I need your help.
Here is my problem.
I tried this query in dbms SQL Server 2008 and I have this output:
QMATNUMBER
1FMOL103623
1FMOL103623
1DLIP69119
1DLIP69119
I need this:
QMATNUMBER
1FMOL103623
1DLIP69119
Tot172742
Can you help me?
Thanks in advance.
SELECT
strDTZZ AS Q,
COALESCE ([MAT], 'Tot') AS [MAT],
NUMBER
FROM
(
SELECT
LEFT (DTZZ, 2) AS strDTZZ,
CASE
WHEN LEFT (TZZ, 2) = '1D' THEN
'LIP'
WHEN LEFT (TZZ, 2) = '1F' THEN
'MOL'
WHEN LEFT (TZZ, 2) = '1G' THEN
'IRT'
WHEN LEFT (TZZ, 2) = '1H' THEN
'MRE'
WHEN LEFT (TZZ, 2) = '1I' THEN
'UOT'
WHEN LEFT (TZZ, 2) = '1M' THEN
'MAL'
WHEN LEFT (TZZ, 2) = '1S' THEN
'RAS'
WHEN LEFT (TZZ, 2) = '1O' THEN
'PMC'
WHEN LEFT (TZZ, 2) = '1P' THEN
'BUP'
WHEN LEFT (TZZ, 2) = '1Q' THEN
'LAC'
ELSE
'CIS'
END AS MAT,
COUNT (*) AS NUMBER,
FROM
dbo_40
GROUP BY
ROLLUP ( LEFT (DTZZ, 2),
CASE
WHEN LEFT (TZZ, 2) = '1D' THEN
'LIP'
WHEN LEFT (TZZ, 2) = '1F' THEN
'MOL'
WHEN LEFT (TZZ, 2) = '1G' THEN
'IRT'
WHEN LEFT (TZZ, 2) = '1H' THEN
'MRE'
WHEN LEFT (TZZ, 2) = '1I' THEN
'UOT'
WHEN LEFT (TZZ, 2) = '1M' THEN
'MAL'
WHEN LEFT (TZZ, 2) = '1S' THEN
'RAS'
WHEN LEFT (TZZ, 2) = '1O' THEN
'PMC'
WHEN LEFT (TZZ, 2) = '1P' THEN
'BUP'
WHEN LEFT (TZZ, 2) = '1Q' THEN
'LAC'
ELSE
'CIS'
END
)
) AS SubQ
WHERE
1 = 1
AND (strDTZZ = '1D' OR strDTZZ = '1F');
August 11, 2012 at 11:35 pm
COALESCE ([MAT], 'Tot') will return string 'Tot' for every MAT that has NULL.
If you want to add Tot as a row you need to use UNION.
--Vadim R.
August 12, 2012 at 3:17 am
No need for union in SQL 2008, grouping sets will work fine.
Try this. Untested because I don't have your tables
GROUP BY GROUPING SETS(
(Mat), ()
)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2012 at 1:41 am
thanks a lot!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply