Show Dollar Amount By Name On A Month To Month Basis I want to show dollar amount by name on a month to month basis, but my syntax gives me an entry for each different dollar amount.  Below is what I get from my query (subset of data)

Name  Value   Month
A     200     2016-01-01 00:00:00-05
A     400     2016-01-03 00:00:00-05


However, this is what I want (ONE entry for salesman and dollar amount for each month)

Name  Value   Month
A     600     2016-01-01 00:00:00-05


What syntax will get me these results?
This is my non-working syntax:

SELECT
na.name As "Name"
,SUM(jl.dollaramt) As "Income"
,DATE_TRUNC('MONTH',jl.dateofwork) As "Month"
from workprovided jl
INNER JOIN people na
ON jl.wpid = na.personid
WHERE CAST(jl.dateofwork As Date) BETWEEN '01/01/2016' AND '04/09/2016'
GROUP BY na.name, jl.dateofwork
ORDER BY na.name ASC
To tackle this problem, I did the following.

Created my tables (workprovided and people):

CREATE TABLE workprovided(wpid VARCHAR(20), dollaramount INTEGER, dateofwork timestamp);

CREATE TABLE people (personid VARCHAR(20), name VARCHAR(20));


Populated my tables:

INSERT INTO workprovided VALUES('A', 200, '2016-01-01 00:00:00-05');
INSERT INTO workprovided VALUES('A', 200, '2016-01-13 00:00:00-05');
INSERT INTO workprovided VALUES('A', 400, '2016-12-01 00:00:00-05');
INSERT INTO workprovided VALUES('A', 500, '2016-12-07 00:00:00-05');


and

INSERT INTO people VALUES ('A', 'Billy');


And then ran the following query, largely based on yours. You were close, just not grouping by the DATE_TRUNC - which means that your results were inconsistent.

SELECT 
  na.name,
  SUM(jl.dollaramount),
  DATE_TRUNC('MONTH', jl.dateofwork)
FROM workprovided jl
INNER JOIN people na
ON jl.wpid = na.personid
GROUP BY na.name, DATE_TRUNC('MONTH', jl.dateofwork)


Result:

name;     sum;           date_trunc
-----     ----           -----------
Billy;    400;           2016-01-01 00:00:00
Billy;    900;           2016-12-01 00:00:00


Note that this sums them by the DATE_TRUNC('MONTH'...) part - which is, if I've understood you correctly, what you're looking for?

For extra credit, I came up with this - nicer formatting and sorting:

SELECT 
  na.name,
  SUM(jl.dollaramount),
  CASE 
    WHEN EXTRACT(MONTH FROM jl.dateofwork) = 1 THEN 'January'
    -- fill in the rest here...
    WHEN EXTRACT(MONTH FROM jl.dateofwork) = 12 THEN 'December'
  END AS Month
FROM workprovided jl
INNER JOIN people na
ON jl.wpid = na.personid
GROUP BY na.name, Month, EXTRACT(MONTH FROM jl.dateofwork)
ORDER BY na.name, EXTRACT(MONTH FROM jl.dateofwork) ASC


Result:

name;     sum;    month
-----     ---     -------
Billy;    400;    January
Billy;    900;    December