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 |

here you used V1, v2 for venu, which is hardcoded. what happens if you dont know the venu names before hand? say the venu names are added and edited at different times?

@oghenez parameterized queries