waylan/sqlite_pivot_tables.md

## sqlite_pivot_tables.md

      
    Raw
  

              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 |