CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
Jake Wagner
Jake Wagner

Reputation: 826

Add a sum total in a subquery

I am trying to add a Total at the bottom and do not seem to comprehend how can I get the syntax to show me the 'Total' at the bottom. I have exhausted my time searching for it online. I need the coalesce snippet because I need to show zeros for the Employees with no Sales. I took a look at this link but I need the Zero in the coalesce.

Add a row for TOTAL in a sql query result

select t2.Employee, coalesce(t1."This Week",0) "This Week"
from mytable t2 left outer join
(select case when grouping(Employee) = 1 then 'Total' else Employee end, sum(Sales) "This Week"
from information
where Week >= DATE '01/01/2017' and Week < DATE '01/31/2017'
and LastUpdate >= DATE '01/01/2017' and LastUpdate < DATE '01/31/2017'
group by Employee with Rollup) t1
on t1.Employee = t2.Employee

Result:

Employee                    This Week 

 Batman                       15
 Penguin                      25
 Joker                        0
 Bane                         5
 Scarecrow                    0
 ------------------->         45

Error:

ERROR:  syntax error at or near "with"
LINE 8: group by Employee with Rollup) t1

Upvotes: 0

Views: 283

Answers (2)

Fabian Pijcke
Fabian Pijcke

Reputation: 3210

You don't need the outer query. Besides the conditions on Week and LastUpdate do not concern the join operation; you can move them to the WHERE clause (which the PostgreSQL optimizer should do itself anyway, but that way your query is more clear):

SELECT
  COALESCE(t2.Employee, 'Total') AS Employee,
  COALESCE(SUM(t1.Sales), 0) AS "This Week"
FROM mytable t2
LEFT JOIN information t1 USING (Employee)
WHERE t1.Week BETWEEN DATE '01/01/2017' AND DATE '01/31/2017'
  AND t1.LastUpdate BETWEEN DATE '01/01/2017' AND DATE '01/31/2017'
GROUP BY GROUPING SETS ((t2.Employee), ());

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can try this with ROLLUP

SELECT coalesce(Employee,'Total'),
       "This Week"
FROM
  (SELECT t2.Employee,
          coalesce(sum(t1.Sales),0) "This Week"
   FROM mytable t2
   LEFT JOIN information t1 ON t1.Employee = t2.Employee
   AND t1.Week >= DATE '01/01/2017'
   AND t1.Week < DATE '01/31/2017'
   AND t1.LastUpdate >= DATE '01/01/2017'
   AND t1.LastUpdate < DATE '01/31/2017'
   GROUP BY rollup(t2.Employee)
  ) x

Upvotes: 1

Related Questions