SQLite: Multiple aggregate columns

Question

I'm a little new to SQL world and still learning the ins and outs of the language.

I have a table with an id, dayOfWeek, and a count for each day. So any given id might appear in the table up to seven times, with a count of events for each day for each id. I'd like to restructure the table to have a single row for each id with a column for each day of the week, something like the following obviously incorrect query:

SELECT id, sum(numEvents where dayOfWeek = 0), sum(numEvents where dayOfWeek = 1) ... from t;

Is there a solid way to approach this?

EDIT:

I'm worried I may not have been very clear. The table would ideally be structured something like this:

id | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday
0  | 13     | 45     | 142     | 3         | 36       | 63     | 15336
1  | 17     | 25     | 45      | 364       | 37       | 540    | 0

So event 0 occurred 13 times on Sunday, 45 on Monday, etc... My current table looks like this:

id | dayOfWeek | count
0  | 0         | 13
0  | 1         | 45
0  | 2         | 142
0  | 3         | 3
0  | 4         | 36
0  | 5         | 63
0  | 6         | 15336
1  | 0         | 17
1  | 1         | 25
...

Hope that helps clear up what I'm after.

score 1 · Answer 1 · answered Jan 07 '14 at 14:19

1

SELECT dayOfWeek, sum(numEvents) as numberOfEvents
FROM t
GROUP BY dayOfWeek;

answered Jan 07 '14 at 14:19

niyou

875
1
11
23

score 1 · Accepted Answer · answered Jan 07 '14 at 14:30

The following is verbose, but should work (generic ideone sql demo unfortunately SqlLite on SqlFiddle is down at the moment):

SELECT id, 
    SUM(case when dayofweek = 1 then numevents else 0 end) as Day1Events,
    SUM(case when dayofweek = 2 then numevents else 0 end) as Day2Events,
    SUM(case when dayofweek = 3 then numevents else 0 end) as Day3Events 
        --, etc...
FROM EventTable
GROUP BY ID;

SQLite: Multiple aggregate columns

2 Answers2