Group by then Sort (SQL Server)

Question

Following the previous question

I have this query:

SELECT  Acc.DocTLItem.TLRef ,
        Acc.DocTLItem.Debit AS deb,
        Acc.DocTLItem.Credit AS cred,
        info.MiladiToShamsi(Acc.DocTLItem.StartDocDate) Date,
        Acc.TL.TLCode ,
        Acc.DocTLItem.DocTLHeaderRef ,
        Acc.DocTLHeader.Num
FROM    Acc.DocTLItem
        INNER JOIN Acc.TL ON Acc.DocTLItem.TLRef = Acc.TL.Id
        INNER JOIN Acc.DocTLHeader ON Acc.DocTLItem.DocTLHeaderRef = Acc.DocTLHeader.Id
        ORDER BY ( CASE WHEN debit > 0 THEN 0  ELSE 1 END ) ,
        Acc.TL.TLCode ,
        debit

Result:

 TLRef  deb cred        Date    TLCode  DocTLHeaderRef  Num
    --------------------------------------------------------------------------
    44   1  0       1396/09/12  111     16           2
    44   1  0       1396/09/21  111     18           4
    28   13 0       1396/09/11  982     15           1
    28   10 0       1396/09/19  982     17           3
    44   0  10      1396/09/19  111     17           3
    44   0  1       1396/09/21  111     18           4
    44   0  9       1396/09/11  111     15           1
    44   0  1       1396/09/12  111     16           2

How can I Group by Date then sort by Date?

I need to generate a result set like this that debt comes first and then ordered by TLCode column after all group by date.

Expected result:

  TLRef deb cred    Date    TLCode  DocTLHeaderRef  Num
    --------------------------------------------------------------------------------
    44  1    0  1396/09/12  111         16           2
    28  13   0  1396/09/11  982         15           1
    28  10   0  1396/09/19  982         17           3
    44  0    9  1396/09/11  111         15           1
    44  0    1  1396/09/12  111         16           2
    44  0    10 1396/09/19  111         17           3
    Sum 24   20             

    44  1   0   1396/09/21  111         18           4
    44  0   1   1396/09/21  111         18           4
    Sum 1   1

Your Sum rows are impossible, given the SQL you posted. Edit your question. — nicomp, Dec 25 '17 at 12:20

Yash · Answer 1 · 2017-12-27T06:19:45.540

May be following query block can help you: This query will work in 4 steps:

--1. Create a temporary table that we can take as base table (#TMP)
Select *
INTO #TMP
From
(
Select 44 as TLRef, 1 as deb, 0 as cred, '1396/09/12' as Date, 111 as TLCode, 16 as DocTLHeaderRef, 2 as Num Union All
Select 44 as TLRef, 1 as deb, 0 as cred, '1396/09/21' as Date, 111 as TLCode, 18 as DocTLHeaderRef, 4 as Num Union All
Select 28 as TLRef, 13 as deb, 0 as cred, '1396/09/11' as Date, 982 as TLCode, 15 as DocTLHeaderRef, 1 as Num Union All
Select 28 as TLRef, 10 as deb, 0 as cred, '1396/09/19' as Date, 982 as TLCode, 17 as DocTLHeaderRef, 3 as Num Union All
Select 44 as TLRef, 0 as deb, 10 as cred, '1396/09/19' as Date, 111 as TLCode, 17 as DocTLHeaderRef, 3 as Num Union All
Select 44 as TLRef, 0 as deb, 1 as cred, '1396/09/21' as Date, 111 as TLCode, 18 as DocTLHeaderRef, 4 as Num Union All
Select 44 as TLRef, 0 as deb, 9 as cred, '1396/09/11' as Date, 111 as TLCode, 15 as DocTLHeaderRef, 1 as Num Union All
Select 44 as TLRef, 0 as deb, 1 as cred, '1396/09/12' as Date, 111 as TLCode, 16 as DocTLHeaderRef, 2 as Num
) X

--2. Group table by "Date" and select sum of "deb", "cred" columns and insert result in another temporary table (#TMP2)
Select null as TLRef, SUM(deb) as deb, SUM(cred) as cred, Date, null as TLCode, null as DocTLHeaderRef, null as Num 
INTO #TMP2
From #TMP 
GROUP BY Date

--3. Union both tables to resulting table gets both detail and grouped data.
Select *
From
(
    Select *, 0 as IsDetail From #TMP
    Union All
    Select *, 1 as IsDetail From #TMP2
) X
Order By Date,IsDetail

--4. Drop both temporary table
DROP TABLE #TMP
DROP TABLE #TMP2

Query works in 4 steps: 1. Create a temporary table that we can take as base table (#TMP) 2. Group table by "Date" and select sum of "deb", "cred" columns and insert result in another temporary table (#TMP2) 3. Union both tables to resulting table gets both detail and grouped data. 4. Drop both temporary table — Yash, Dec 26 '17 at 14:58

score 0 · Answer 2 · answered Dec 26 '17 at 06:10

You can try this for sorting.

;WITH CTE AS (
    SELECT  Acc.DocTLItem.TLRef ,
            Acc.DocTLItem.Debit AS deb,
            Acc.DocTLItem.Credit AS cred,
            info.MiladiToShamsi(Acc.DocTLItem.StartDocDate) Date,
            Acc.TL.TLCode ,
            Acc.DocTLItem.DocTLHeaderRef ,
            Acc.DocTLHeader.Num,
            ROW_NUMBER() OVER(PARTITION BY Acc.DocTLItem.Debi, Acc.DocTLItem.Credit, Acc.TL.TLCode  ORDER BY Acc.DocTLItem.StartDocDate ) AS RN
    FROM    Acc.DocTLItem
            INNER JOIN Acc.TL ON Acc.DocTLItem.TLRef = Acc.TL.Id
            INNER JOIN Acc.DocTLHeader ON Acc.DocTLItem.DocTLHeaderRef = Acc.DocTLHeader.Id

)
SELECT * FROM CTE
ORDER BY 
    RN,
    ( CASE WHEN deb > 0 THEN 0  ELSE 1 END ) ,
    TLCode ,
    [Date],
    deb

Group by then Sort (SQL Server)

2 Answers2

Linked