SQLITE combine select result set

Question

I have a table like this:

|-----------------------------|
|someText | Date | someInteger|
|-----------------------------|

What i want is to select a query like this:

|-----------------------------------|
|someText | Jan | Feb | March | ... |
|-----------------------------------|

Basicaly i want to summ all the someInteger grouped by someText for each single month, aka

Select Sum(someInteger) From TABLE
Where Date = [month]
GroupBy someText

...and then, if possible, i would like to combine all the 12 queries of this into one resulting table for ease of use and possibly some optimisation.

Sample data as create table and insert statements for easy importing and the expected results of that data would be useful. — Shawn, Sep 18 '19 at 06:45
Maybe you can relate to pivot system : https://stackoverflow.com/questions/1237068/pivot-in-sqlite — Jaisus, Sep 18 '19 at 09:33

score 0 · Answer 1 · answered Sep 18 '19 at 11:28

0

How about group by?

Select strftime('%y-%m', date) as yyyymm,
       sum(someInteger)
from t
group by strftime('%y-%m', date);

answered Sep 18 '19 at 11:28

Gordon Linoff

1,242,037
58
646
786

score 0 · Accepted Answer · answered Sep 18 '19 at 12:55

0

With conditional aggregation (assuming that all the dates are in the same year):

select 
  someText,
  sum(case when strftime('%m', date) = '01' then someInteger end) Jan,
  sum(case when strftime('%m', date) = '02' then someInteger end) Feb,
  sum(case when strftime('%m', date) = '03' then someInteger end) Mar,
  ...................................................................
from tablename
group by someText

See the demo.

answered Sep 18 '19 at 12:55

forpas

160,666
10
38
76

Any idea why strftime may return empty string? – Sir Knight Sep 23 '19 at 05:59
The format of the date must be `yyyy-mm-dd`. If not you get null. – forpas Sep 23 '19 at 06:56

SQLITE combine select result set

2 Answers2