0

I am using SQLite as my database in my C++ application. I have a table similar to one given below

Day | PartyA | PartyB | Amount
--------------------------------
1   |    X   |   Y    | 25
1   |    X   |   K    | 10
1   |    Y   |   M    | 30
1   |    Z   |   L    | 20
2   |    X   |   Y    | 10
2   |    X   |   K    | 30
2   |    Y   |   M    | 50
2   |    Z   |   L    | 5
3   |    X   |   K    | 60
3   |    Y   |   M    | 15

and I want to get something like the table below. This is similar to MS Excel pivot table; the final table has unique PartyA - PartyB pairs and a column named TotalAmount has the summation of Amount values given in the previous table.

PartyA | PartyB | TotalAmount
--------------------------------
   X   |   Y    | 35
   X   |   K    | 100
   Y   |   M    | 95
   Z   |   L    | 25

Although, the C/C++ API interface allows to implement this, I want to do it by using SQL queries.

Your assistance will be much appreciated on the matter. Thanks in advance

fnisi
  • 1,181
  • 1
  • 14
  • 24

2 Answers2

1

You need to group by PartyA and PartyB and then take the sum of Amount for each group. Use the following query:

SELECT PartyA, PartyB, SUM(Amount) TotalAmount FROM MyTable 
GROUP BY PartyA, PartyB;

This groups the matching pair of PartyA and PartyB before applying the sum function.

SQLFiddle demo here.

COMMENT:

You have mentioned 'pivot' in your title. But as you can see from the above query, you don't need to use a pivot for your requirement. Also, SQLite doesn't support the pivot function. But a workaround for pivot is possible, as shown in this question. Just added here for your reading.

Community
  • 1
  • 1
CodeNewbie
  • 2,003
  • 16
  • 29
  • If truth to be told, not sure about the Pivot concept in SQL, that's why I explicitly stated Excel style in my post, as this is the only pivot I know :) – fnisi May 25 '15 at 04:46
  • You should most definitely read up on pivot then. It is quite a handy thing and you might come across some use cases in the future. :) – CodeNewbie May 25 '15 at 04:48
1

This would be sql way of doing this 

select PartyA,PartyB, sum(Amount) as TotalAmount from <giventable> 
group by PartyA, PartyB

Also refer here for more

Biswanath
  • 9,075
  • 12
  • 44
  • 58