Post reply

How to return non-grouped items from a GROUP BY statement?

  • March 2, 2018 at 6:45 am

    #336429

    Hi all

    I have the following data in table MyTrans:

    Account           Date                  Amount            RecID
    100000           15/02/2018       100.00             1
    100000           25/02/2018       150.00             2
    100000           25/02/2018       50.00               3
    100002           15/02/2018       200.00             4
    100002           15/02/2018       1500.00           5
    100002           15/02/2018       -250.00            6
    100002           25/02/2018       145.00             7
    100002           25/02/2018       -145.00            8
    100002           01/03/2018       -25.00              9
    100003           01/03/2018       1950.00           10

    See below SQL to create this table:

    create table #MyTrans
    (
      Account nvarchar(6),
      Date datetime,
        Amount numeric(18,2),
        RecID varchar(2)
    )

    INSERT INTO #MyTrans(Account, Date, Amount, RecID) values
    ('100000','15/02/2018','100.00','1'),
    ('100000','25/02/2018','150.00','2'),
    ('100000','25/02/2018','50.00','3'),
    ('100002','15/02/2018','200.00','4'),
    ('100002','15/02/2018','1500.00','5'),
    ('100002','15/02/2018','-250.00','6'),
    ('100002','25/02/2018','145.00','7'),
    ('100002','25/02/2018','-145.00','8'),
    ('100002','01/03/2018','-25.00','9'),
    ('100003','01/03/2018','1950.00','10')

    I want to return only those records where the total per Account and then per Date, is greater than 0, eg for the above dataset I want to return:

    Account           Date                  Amount            RecID
    100000           15/02/2018       100.00             1
    100000           25/02/2018       150.00             2
    100000           25/02/2018       50.00               3
    100002           15/02/2018       200.00             4
    100002           15/02/2018       1500.00           5
    100002           15/02/2018       -250.00            6
    100003           01/03/2018       1950.00           10

    My query is currently as follows:

    Select Account, Date, sum(Amount) as Amount, RecID
    From #MyTrans
    group by Account, Date
    Having sum(Amount)>0

    However, I cannot do it this way as the RecID column is not in the Group By statement, even though I dont want to grouo by this column. I am unsure how I can return the RecID and have the group by statement not group on this column. Does anyone have any ideas?

    Cheers for all help

  • March 2, 2018 at 7:11 am

    #1981762

    WITH Grouped AS (
        SELECT
             Account
        ,    Date
        ,    SUM(Amount) OVER (PARTITION BY Account, Date) AS Amount
        ,    RecID
        FROM #MyTrans
        )
    SELECT
         Account
    ,    Date
    ,    Amount
    ,    RecID
    FROM Grouped
    WHERE Amount > 0

    John

  • March 2, 2018 at 7:49 am

    #1981767

    Thanks John, thats brill!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply