Search code examples
sqlpostgresqlpivotdynamic-pivot

SQL query for sales report by date

I have a table of sales leads:

CREATE TABLE "lead" (
    "id" serial NOT NULL PRIMARY KEY,
    "marketer" varchar(500) NOT NULL,
    "date_set" varchar(500) NOT NULL
)
;
INSERT INTO lead VALUES (1, 'Joe', '05/01/13');
INSERT INTO lead VALUES (2, 'Joe', '05/02/13');
INSERT INTO lead VALUES (3, 'Joe', '05/03/13');
INSERT INTO lead VALUES (4, 'Sally', '05/03/13');
INSERT INTO lead VALUES (5, 'Sally', '05/03/13');
INSERT INTO lead VALUES (6, 'Andrew', '05/04/13');

I want to produce a report that summarizes the number of records each marketer has for each day. It should look like this:

| MARKETER | 05/01/13 | 05/02/13 | 05/03/13 | 05/04/13 |
--------------------------------------------------------
|      Joe |        1 |        1 |        1 |        0 |
|    Sally |        0 |        0 |        2 |        1 |
|   Andrew |        0 |        0 |        0 |        1 |

What's the SQL query to produce this?

I have this example set up on SQL Fiddle: http://sqlfiddle.com/#!12/eb27a/1

Solution

  • Pure SQL cannot produce such structure (it is two dimensional, but sql return plain list of records).

    You could make query like this:

    select marketer, date_set, count(id) 
from lead
group by marketer, date_set;

    And vizualise this data by your reporting system.