Skip to content

Instantly share code, notes, and snippets.

@waylan
Last active November 17, 2022 08:43
Show Gist options
  • Save waylan/6555316 to your computer and use it in GitHub Desktop.
Save waylan/6555316 to your computer and use it in GitHub Desktop.
Sqlite pivot tables.

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
Copy link

oghenez commented Jan 12, 2015

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?

@HDegano
Copy link

HDegano commented Jan 13, 2015

@oghenez parameterized queries

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment