Only sum values for groups with more than one row

Question

I have this query:

SELECT extract(year from date1), extract(month from date1), spending    
FROM ( SELECT *, COUNT(*) OVER(PARTITION BY CONCAT(extract(year FROM date1), extract(month FROM date1))) N
       FROM table) as A    
WHERE N > 1    
GROUP BY date1    
ORDER BY date1 ASC;

With this result:

I need to sum the field spending only when there are more than one row with equla year and month. Desired result:

year    month   spending
----    -----   --------
2015     1        5424
2016     1      605886
2016     5      xxxxxx
....     ..     ......

Answer 1

ok, I found the solution: HAVING :

SELECT extract(year from date1), extract(month from date1), spending    
FROM table  
GROUP BY extract(month from date1)), extract(year from date1), extract(month from date1)
HAVING count (CONCAT(extract(year from date1), extract(month from date1))) > 1
ORDER BY extract(year from date1), extract(month from date1) ASC; 

in case it helps someone.

Answer 2

This can be simpler and faster using date_trunc() and some simplifications:

SELECT date_trunc('month', date1)::date AS month
     , sum(spending)                    AS sum_spending
     , count(*)                         AS count_rows  -- optional addition
FROM   table
GROUP  BY 1
HAVING count(*) > 1
ORDER  BY 1;

Only returns the sum of spendings for months with more than one row.

If you need to display separate year and month numbers, you could use above query in a subquery, still faster:

SELECT extract(year  FROM month)::int AS year
     , extract(month FROM month)::int AS month
     , sum_spending, count_rows
FROM (
   SELECT date_trunc('month', date1)::date AS month
        , sum(spending)                    AS sum_spending
        , count(*)                         AS count_rows  -- optional
   FROM   table
   GROUP  BY 1
   HAVING count(*) > 1
   ORDER  BY 1
   ) sub;

Or extract the numbers directly like in your solution, but just use the much faster count(*) in the HAVING clause:

SELECT extract(year  FROM date1)::int AS year
     , extract(month FROM date1)::int AS month
     , sum(spending)                  AS sum_spending
     , count(*)                       AS count_rows  -- optional
FROM   table
GROUP  BY 1, 2
HAVING count(*) > 1
ORDER  BY 1, 2;

1, 2 are (totally optional) positional references to shorten the syntax, so we don't have to repeat the expressions in the SELECT list. Example:

• Select first row in each GROUP BY group?

The cast to integer (::int) is also optional. The generic return type of extract is double precision, but year and date can be cast to integer safely. Smaller, faster and more adequate.

Answer 3

try this

SELECT extract(year from date1), extract(month from date1), sum(spending)    
FROM ( SELECT *, COUNT(*) OVER(PARTITION BY CONCAT(extract(year FROM date1), extract(month FROM date1))) N
       FROM table) as A    
WHERE N > 1    
GROUP BY extract(year from date1),extract(month from date1)
ORDER BY extract(year from date1),extract(month from date1) ASC;