Sqlite pivot tables.

sqlite_pivot_tables.md

Given the following table:

CREATE TABLE "venue" 
(
    "id" INTEGER NOT NULL PRIMARY KEY, 
    "name" VARCHAR(255) NOT NULL
);

INSERT INTO "venue" ("name") VALUES ('V1');
INSERT INTO "venue" ("name") VALUES ('V2');

CREATE TABLE "event" 
(
    "id" INTEGER NOT NULL PRIMARY KEY, 
    "date" DATE NOT NULL, 
    "venue_id" INTEGER NOT NULL REFERENCES "venue" ("id") 
);

CREATE INDEX "event_venue_id" ON "event" ("venue_id");

INSERT INTO "event" ("venue_id", "date") VALUES (1, date(2013-09-19));
INSERT INTO "event" ("venue_id", "date") VALUES (2, date(2013-09-04));
INSERT INTO "event" ("venue_id", "date") VALUES (2, date(2013-08-10));
INSERT INTO "event" ("venue_id", "date") VALUES (2, date(2013-08-06));
INSERT INTO "event" ("venue_id", "date") VALUES (1, date(2013-08-13));

We then have the following tables:

SELECT * from event;

| id |       date | venue_id |
|----|------------|----------|
|  1 | 2013-09-19 |        1 |
|  2 | 2013-09-04 |        2 |
|  3 | 2013-08-10 |        2 |
|  4 | 2013-08-06 |        2 |
|  5 | 2013-08-13 |        1 |
|  6 | 2012-08-13 |        1 |  << this is 2012

SELECT * FROM venue;

| id | name |
|----|------|
|  1 |   V1 |
|  2 |   B2 |

This query:

SELECT venue.name as Venue, 
       substr(event.date, 6, 2) AS Month, 
       Count(venue.id) AS Count 
FROM   event 
       INNER JOIN venue 
           ON (event.venue_id = venue.id) 
WHERE  (substr(event.date, 1, 4) = '2013') 
GROUP BY Month, Venue
ORDER BY Month
;

Gives this result:

| Venue | Month | Count |
|-------|-------|-------|
|    V1 |    08 |     1 |
|    V2 |    08 |     2 |
|    V1 |    09 |     1 |
|    V2 |    09 |     1 |

Which gives us the info we need (in a not so usful format) but we can do better. Use case to emulate PIVOT in Sqlite:

SELECT substr(event.date, 6, 2) AS Month,
       Count(case when venue.name = 'V1' then venue.id end) as V1,
       Count(case when venue.name = 'V2' then venue.id end) as V2,
       Count(venue.id) AS Total 
FROM   event 
       LEFT OUTER JOIN venue 
            ON (event.venue_id = venue.id) 
WHERE  (substr(event.date, 1, 4) = '2013') 
GROUP BY Month
ORDER BY Month
;

Which gives us what we want:

| Month | V1 | V2 | Total |
|-------|----|----|-------|
|    08 |  1 |  2 |     3 |
|    09 |  1 |  1 |     2 |

See this at sqlfiddle.

Then we need to get the last row for column totals:

SELECT "Total" AS Month,
        Count(case when venue.name = 'V1' then venue.id end) as V1,
        Count(case when venue.name = 'V2' then venue.id end) as V2,
        Count(venue.id) AS Total 
FROM   event 
        LEFT OUTER JOIN venue 
            ON (event.venue_id = venue.id) 
WHERE  (substr(event.date, 1, 4) = '2013') 
GROUP BY Month

Which gives us this (sqlfiddle):

| Month | V1 | V2 | Total |
|-------|----|----|-------|
| Total |  2 |  3 |     5 |

In our application code we then append that last row to the first set and we get:

| Month | V1 | V2 | Total |
|-------|----|----|-------|
|    08 |  1 |  2 |     3 |
|    09 |  1 |  1 |     2 |
| Total |  2 |  3 |     5 |

@oghenez parameterized queries