Search code examples
sqlsqlitewindow-functions

SQLite How to sum columns based on one one column and group based on a different column

For example, table TableQ looks like this:

Item Price Tag
Item A 2 X
Item A 4 Y
Item B 1 Z

I want my output to be like this:

Item Price Tag
Item A 6 X
Item A 6 Y
Item B 1 Z

When I try

select distinct * from TableQ as Q1 left join ( select sum(Q2.Price) as PriceSum from TableQ as Q2 )

I get

Item Price Tag PriceSum
Item A 2 X 7
Item A 4 Y 7
Item B 1 Z 7
Solution

  • Since version 3.25.0 (2018-09-15) SQLite supports window functions:

    SELECT Item,
       SUM(Price) OVER (PARTITION BY Item) AS Price,
       Tag 
FROM TableQ;

    See the demo.