SQL Group multiple rows into 2 rows

Question

I have the query below:

SELECT 
    Sender, 
    DATEADD(dd, DATEDIFF(dd, 0, Date_Reported) / 7 * 7, 0) AS Weekdate, 
    COUNT(status) AS TranCount
FROM
    tx
WHERE 
    customer = 'ABC'
    AND DATEADD(dd, DATEDIFF(dd, 0, Date_Reported) / 7 * 7, 0) >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) / 7 * 7, 0)
    AND DATEADD(dd, DATEDIFF(dd, 0, Date_Reported) / 7 * 7, 0) < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) 
GROUP BY 
    Sender, DATEADD(dd,DATEDIFF(dd,0,Date_Reported)/7 * 7,0)

This returns a list of all transactions for each sender. What I want is only 2 rows of two sender types- one that is sender = "AA" and the other sender NOT IN "AA".

How do I incorporate this into my script? At the moment I get a list of 50 senders, but I just want 2 as stated above

Expected Result

Sender            |  Weekdate      |  TranCount
AA                |    25/06/2018  |      33
"Not Sender AA"   |    25/06/2018  |      26

the clause is customer = ''AA" which is separate to sender (i have edited to avoid confusion) — Taz, Jun 28 '18 at 13:05
The sender "not sender AA" should be a sum of all non- AA transactions grouped as one row rather than 40+ rows — Taz, Jun 28 '18 at 13:11
Please add DDL+DML (queries to create the table and insert the sample data) — Ronen Ariely, Jun 28 '18 at 13:12
@iamdave Not necessarily in [SQL Server's integer math](https://stackoverflow.com/questions/11719044/how-to-get-a-float-result-by-dividing-two-integer-values). The queries `select (1 / 7) * 7` and `select (1.0 / 7) * 7` give very different answers - neither of which is 1. — tarheel, Jun 28 '18 at 13:27
@iamdave not quite. It is doing integer math which is essentially removing the modulo result. For example, the datediff portion today returns 43277, which when you divide by 7 then multiply by 7 returns 43274. Of course that should be documented very clearly in the comments to avoid issues in the future. :) — Sean Lange, Jun 28 '18 at 13:27

score 2 · Accepted Answer · answered Jun 28 '18 at 13:18

2

With a simple CASE statement:

SELECT 
    CASE Sender WHEN 'AA' THEN Sender ELSE 'Not Sender AA' END AS Sender, 
    DATEADD(dd, DATEDIFF(dd, 0, Date_Reported) / 7 * 7, 0) AS Weekdate, 
    COUNT(status) AS TranCount
FROM
    tx
WHERE 
    customer = 'ABC'
    AND DATEADD(dd, DATEDIFF(dd, 0, Date_Reported) / 7 * 7, 0) >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) / 7 * 7, 0)
    AND DATEADD(dd, DATEDIFF(dd, 0, Date_Reported) / 7 * 7, 0) < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) 
GROUP BY 
    CASE Sender WHEN 'AA' THEN Sender ELSE 'Not Sender AA' END, DATEADD(dd,DATEDIFF(dd,0,Date_Reported)/7 * 7,0)

Note you have to put the same CASE statement in the GROUP BY clause.

answered Jun 28 '18 at 13:18

Gareth Lyons

1,942
12
14

Thank you so much gareth works perfectly. Worth a shout, this limits it to the latest week, any idea how i can do it for the last 12 weeks? – Taz Jun 28 '18 at 13:29
@Taz it shows the last week because that is what your where predicates limit it to. Change the where clause to suit what you want returned. – Sean Lange Jun 28 '18 at 13:33
thanks @SeanLange but will that not just extend the range? I want 12 different dates for each of the 2 rows so 24 rows? – Taz Jun 28 '18 at 13:59
1

Of course it would extend the range, that is what you want right? But it seems that then you want to group them by week so you would need to adjust your group by accordingly. Make sense? – Sean Lange Jun 28 '18 at 14:01
yes that is what i want but i am kind of confused how i would do this. – Taz Jun 28 '18 at 14:07
Far as I can tell your query should do this already? (Groups by the Monday date for the week of Date_Reported). Just need to change the where clause. – Gareth Lyons Jun 28 '18 at 14:18

Andrey Stukalin · Answer 2 · 2018-06-28T13:22:01.373

How about union? Something like (Sorry don't have MS SQL Studio right there)

SELECT 
    Sender, 
    DATEADD(dd, DATEDIFF(dd, 0, Date_Reported) / 7 * 7, 0) AS Weekdate, 
    COUNT(status) AS TranCount
FROM
    tx
WHERE 
    sender = 'AA' and 
   customer = 'ABC'
    AND DATEADD(dd, DATEDIFF(dd, 0, Date_Reported) / 7 * 7, 0) >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) / 7 * 7, 0)
    AND DATEADD(dd, DATEDIFF(dd, 0, Date_Reported) / 7 * 7, 0) < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) 
GROUP BY 
    Sender, DATEADD(dd,DATEDIFF(dd,0,Date_Reported)/7 * 7,0)
union 
SELECT 
    'Not Sender AA', 
    DATEADD(dd, DATEDIFF(dd, 0, Date_Reported) / 7 * 7, 0) AS Weekdate, 
    COUNT(status) AS TranCount
FROM
    tx
WHERE 
    Sender <> 'AA' and
    customer = 'ABC'
    AND DATEADD(dd, DATEDIFF(dd, 0, Date_Reported) / 7 * 7, 0) >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) / 7 * 7, 0)
    AND DATEADD(dd, DATEDIFF(dd, 0, Date_Reported) / 7 * 7, 0) < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) 
GROUP BY 
    DATEADD(dd,DATEDIFF(dd,0,Date_Reported)/7 * 7,0)

This is really good but only issue is i still have a list of rows that say "Not Sender AA" rather than that as one row — Taz, Jun 28 '18 at 13:19
Ah, right, it uses `GROUP BY Sender`. It should be removed then and adjusted accordingly if grouping by `dateadd` is not enough. Fixed my answer — Andrey Stukalin, Jun 28 '18 at 13:21

SQL Group multiple rows into 2 rows

2 Answers2