Compare grouped by rows on a single row

Question

I have a table with a list of different amounts, and I want to group these amounts from different sources to see if they match. So my table looks something like this:

id name amount description startDate endDate location type sourceId linkId isMatch A111 name1 111.11 desc 2019-06-06T00:00:00Z 2019-06-06T00:00:00Z location a INTERNAL A111X111 True A111 name1 222.22 desc 2019-06-06T00:00:00Z 2019-06-06T00:00:00Z location b INTERNAL A111X111 True A111 name1 555.55 desc 2019-06-06T00:00:00Z 2019-06-06T00:00:00Z location a&b INTERNAL A111X111 True A111 name1 444.44 desc 2019-06-06T00:00:00Z 2019-06-06T00:00:00Z location a EXTERNAL A111X111 True A111 name1 444.44 desc 2019-06-06T00:00:00Z 2019-06-06T00:00:00Z location b EXTERNAL A111X111 True

I can group them like so:

SELECT a.name, a.id, SUM(a.amount) AS total, a.description, DATEDIFF(day, a.startDate, a.endDate) AS days, a.location, a.sourceId, a.linkId, a.isMatch FROM DataHistory a GROUP BY a.name, a.id, a.description, DATEDIFF(day, a.startDate, a.endDate), a.location, a.sourceId, a.linkId, a.isMatch

To get almost what I want:

name id total description days location sourceId linkId isMatch name1 A111 888.88 desc 0 location EXTERNAL A111X111 True name1 A111 888.88 desc 0 location INTERNAL A111X111 True

But what I want is to have a column with the difference between the two amounts (internal and external) and so I actually want these two records to appear on a single line (and also because there will actually be many such pairs of these records). In the example, the difference is zero, but I am going to be looking for the cases when the difference is not zero. It could be that there are no external or internal records (hence 0 for the amount), or just that the amounts sum up to different amounts. I thought about perhaps inserting the grouped records to a temp table and then trying some sort of self-join, but I am not sure if this will work in all cases, and it definitely doesn't seem efficient if I actually have many such records.

I also created a SQL Fiddle here: http://www.sqlfiddle.com/#!18/7a5ff3/1

Any help would be appreciated.

@cuongle Hi cuongle. I am not sure what you mean? In the sample query I gave, the sourceId is in the group by. — C. Helling
– C. Helling, Commented Jun 7, 2019 at 13:45

Michał Turczyn · Accepted Answer · 2019-06-07 13:49:03Z

Try this, drop sourceId from group by and from select and sum all total, but for external take -a.amount. That will give you the difference:

SELECT a.name, a.id, SUM(CASE WHEN a.sourceId = 'INTERNAL' THEN a.amount ELSE -a.amount END) AS total, a.description, DATEDIFF(day, a.startDate, a.endDate) AS days, a.location, a.linkId, a.isMatch FROM DataHistory a GROUP BY a.name, a.id, a.description, DATEDIFF(day, a.startDate, a.endDate), a.location, a.linkId, a.isMatch

Serg · Accepted Answer · 2019-06-07 13:50:44Z

You need pivoting. As you are grouping anyway, conditional aggregation is most natural way to go

SELECT a.name, a.id, SUM(CASE a.sourceId WHEN 'INTERNAL' THEN a.amount ELSE 0 END) AS totalINTERNAL, SUM(CASE a.sourceId WHEN 'EXTERNAL' THEN a.amount ELSE 0 END) AS totalEXTERNAL, a.description, DATEDIFF(day, a.startDate, a.endDate) AS days, a.location, a.linkId, a.isMatch FROM DataHistory a GROUP BY a.name, a.id, a.description, DATEDIFF(day, a.startDate, a.endDate), a.location, a.linkId, a.isMatch

Collectives™ on Stack Overflow

Compare grouped by rows on a single row

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related