Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31

    Unanswered: numeric ORDER BY problem after GROUP BY WITH ROLLUP

    Hi!

    I have the following statement ...

    Code:
    SELECT CASE WHEN GROUPING(<value1>) = 1 THEN 'sum' ELSE CONVERT(varchar,<value1>) END <value1>, COUNT(*) AS quantity
    FROM <mytable>
    GROUP BY <value1> WITH ROLLUP ORDER BY <value1>
    and receive a table like ...
    Code:
    value1 quantity
    1	2
    10	9
    100	1
    101	1
    109	1
    11	5
    12	3
    120	2
    121	1
    sum	25
    How can I now order numerically for value1???

    Without renaming the return value from grouping to sum it works, because I don't need to convert value1 to varchar. But than I can only rename the return value to 999 or something like that.

    Please help! Thank you in advance...

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    In SQL Server 2008 use grouping sets instead. Note this is standard SQL syntax unlike ROLLUP. ROLLUP is deprecated.

    Code:
    SELECT
     COALESCE(CAST(value1 AS VARCHAR(10)),'sum') value1,
     quantity
    FROM
    (
     SELECT value1, COUNT(*) AS quantity
     FROM mytable
     GROUP BY GROUPING SETS (value1,())
    ) t
    ORDER BY t.value1 ;
    If you are using 2005 then order your query like this:

    ... ORDER BY GROUPING(value1), mytable.value1 ;

  3. #3
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31
    Hi dportas!

    Thx for the hint with standard SQL syntax. But nothing changed with your query, still the same result

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    It seems to work for me (see below). Please post enough code to reproduce your problem, including a CREATE TABLE script so we know what the data types are:

    CREATE TABLE mytable (value1 INT NOT NULL /* KEY ?? */);

    INSERT INTO mytable (value1)
    SELECT TOP (2) 1 FROM sys.objects UNION
    SELECT TOP (9) 10 FROM sys.objects UNION
    SELECT TOP (1) 100 FROM sys.objects UNION
    SELECT TOP (5) 11 FROM sys.objects ;

    SELECT
    COALESCE(CAST(value1 AS VARCHAR(10)),'sum') value1,
    quantity
    FROM
    (
    SELECT value1, COUNT(*) AS quantity
    FROM mytable
    GROUP BY GROUPING SETS (value1,())
    ) t
    ORDER BY t.value1 ;

    value1 quantity
    ---------- -----------
    sum 4
    1 1
    10 1
    11 1
    100 1

    (5 row(s) affected)

  5. #5
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31
    OK I will try it, but my original query is a bit more complex...

    the query which works best for me is:
    Code:
    SELECT maxEES, ISNULL([1],0) AS [VDI2=1], ISNULL([2],0) AS [VDI2=2], ISNULL([3],0) AS [VDI2=3], ISNULL([4],0) AS [VDI2=4], ISNULL([999],0) AS [sumVDI2] FROM (
    	SELECT CASE WHEN maxEES IS NULL THEN '999' ELSE maxEES END maxEES, CASE WHEN VDI2 IS NULL THEN '999' ELSE VDI2 END VDI2, COUNT(*) AS quantity FROM (
    		SELECT FALL, BETNR, VDI2, MAX(sumEES) AS maxEES, MIN(sumKNR) AS minKNR FROM (
    			SELECT r.FALL, r.BETNR, r.VDI2, SUM(r.EES) AS sumEES, SUM(r.KNR) AS sumKNR
    			FROM [FZG] f INNER HASH JOIN [FZGA] a ON f.FALL = a.FALL AND f.BETNR = a.BETNR
    			INNER HASH JOIN [REKO] r ON r.FALL = f.FALL AND r.BETNR = f.BETNR
    			WHERE a.MAXDEFZ LIKE 'F[1-4]'
    			GROUP BY r.FALL, r.BETNR, r.VDI2
    		) AS sumTAB GROUP BY FALL, BETNR, VDI2
    	) AS maxTAB GROUP BY maxEES, VDI2 WITH CUBE
    ) AS sourceTAB
    PIVOT (SUM(quantity) FOR VDI2 IN ([1], [2], [3], [4], [999])) AS pivotTAB;
    Code:
    maxEES	VDI2=1	VDI2=2	VDI2=3	VDI2=4	sumVDI2
    0	56	11	0	5	72
    1	281	14	2	14	311
    2	181	13	4	9	207
    3	104	28	11	23	166
    4	56	6	3	13	78
    5	131	37	37	55	260
    ...
    100	1	0	0	0	1
    101	1	0	0	0	1
    109	1	0	0	0	1
    120	2	0	0	0	2
    121	1	0	0	0	1
    999	4293	350	243	368	5254
    maxEES is ordered numerically and the sum is called 999.

    Now I want to rename 999 in sumEES, but can't order int and varchar. So I did that:
    Code:
    SELECT maxEES, ISNULL([1],0) AS [VDI2=1], ISNULL([2],0) AS [VDI2=2], ISNULL([3],0) AS [VDI2=3], ISNULL([4],0) AS [VDI2=4], ISNULL([999],0) AS [sumVDI2] FROM (
    	SELECT CASE WHEN maxEES IS NULL THEN 'sumEES' ELSE CONVERT(varchar,maxEES) END maxEES, CASE WHEN VDI2 IS NULL THEN '999' ELSE VDI2 END VDI2, COUNT(*) AS quantity FROM (
    		SELECT FALL, BETNR, VDI2, MAX(sumEES) AS maxEES, MIN(sumKNR) AS minKNR FROM (
    			SELECT r.FALL, r.BETNR, r.VDI2, SUM(r.EES) AS sumEES, SUM(r.KNR) AS sumKNR
    			FROM [FZG] f INNER HASH JOIN [FZGA] a ON f.FALL = a.FALL AND f.BETNR = a.BETNR
    			INNER HASH JOIN [REKO] r ON r.FALL = f.FALL AND r.BETNR = f.BETNR
    			WHERE a.MAXDEFZ LIKE 'F[1-4]'
    			GROUP BY r.FALL, r.BETNR, r.VDI2
    		) AS sumTAB GROUP BY FALL, BETNR, VDI2
    	) AS maxTAB GROUP BY maxEES, VDI2 WITH CUBE
    ) AS sourceTAB
    PIVOT (SUM(quantity) FOR VDI2 IN ([1], [2], [3], [4], [999])) AS pivotTAB;
    Code:
    maxEES	VDI2=1	VDI2=2	VDI2=3	VDI2=4	sumVDI2
    0	56	11	0	5	72
    1	281	14	2	14	311
    10	186	39	44	41	310
    100	1	0	0	0	1
    101	1	0	0	0	1
    109	1	0	0	0	1
    11	69	8	6	5	88
    12	170	15	10	17	212
    ...
    9	61	8	9	4	82
    91	0	1	0	0	1
    92	1	0	0	0	1
    93	1	0	0	0	1
    95	1	0	0	0	1
    98	2	0	0	0	2
    sumEES	4293	350	243	368	5254
    maxEES is ordered alphanumerically - NOT good!

    Now your solutuion with COALESCE:
    Code:
    SELECT maxEES, ISNULL([1],0) AS [VDI2=1], ISNULL([2],0) AS [VDI2=2], ISNULL([3],0) AS [VDI2=3], ISNULL([4],0) AS [VDI2=4], ISNULL([999],0) AS [sumVDI2] FROM (
    	SELECT COALESCE(CAST(maxEES AS varchar), 'sumEES') AS maxEES, CASE WHEN VDI2 IS NULL THEN '999' ELSE VDI2 END VDI2, COUNT(*) AS quantity FROM (
    		SELECT FALL, BETNR, VDI2, MAX(sumEES) AS maxEES, MIN(sumKNR) AS minKNR FROM (
    			SELECT r.FALL, r.BETNR, r.VDI2, SUM(r.EES) AS sumEES, SUM(r.KNR) AS sumKNR
    			FROM [FZG] f INNER HASH JOIN [FZGA] a ON f.FALL = a.FALL AND f.BETNR = a.BETNR
    			INNER HASH JOIN [REKO] r ON r.FALL = f.FALL AND r.BETNR = f.BETNR
    			WHERE a.MAXDEFZ LIKE 'F[1-4]'
    			GROUP BY r.FALL, r.BETNR, r.VDI2
    		) AS sumTAB GROUP BY FALL, BETNR, VDI2
    	) AS maxTAB GROUP BY maxEES, VDI2 WITH CUBE
    ) AS sourceTAB
    PIVOT (SUM(quantity) FOR VDI2 IN ([1], [2], [3], [4], [999])) AS pivotTAB;
    Code:
    maxEES	VDI2=1	VDI2=2	VDI2=3	VDI2=4	sumVDI2
    0	56	11	0	5	72
    1	281	14	2	14	311
    10	186	39	44	41	310
    100	1	0	0	0	1
    101	1	0	0	0	1
    109	1	0	0	0	1
    11	69	8	6	5	88
    12	170	15	10	17	212
    ...
    9	61	8	9	4	82
    91	0	1	0	0	1
    92	1	0	0	0	1
    93	1	0	0	0	1
    95	1	0	0	0	1
    98	2	0	0	0	2
    sumEES	4293	350	243	368	5254
    maxEES is ordered still alphanumerically - produces same results as mine but is shorter!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •