Skip to content
Advertisement

SQL group and summing at new row

I have an issue where I am trying to create a new “row” (not part of the database) where it only gives me the SUM of the “count” column. I am wondering if that is possible?

I tried adding the SUM(count(cost) as “total product cost” OVER() AS Total_Count; but that just creates a new column with the summed amount of 550.00 displayed in each row. I want that 550.00 to be at the bottom as the final count of that column.

select product, count(cost) as 'total product cost'

from product_Table

where product_Tier = 'grocery'

and as_of_date = '8/13/2020'

Group By product

Results:

product   total product cost
corn       250.00
tomatoes   300.00
SUM        550.00          <--trying to create a row like this.

Advertisement

Answer

Most databases support the standard grouping sets functionality:

select coalesce(product, 'Total'), count(cost) as totalproductcost
from product_Table
where product_Tier = 'grocery' and
      as_of_date = '2020-08-13'
group by grouping sets ( product, () );

Some databases support rollup but not grouping sets. The syntax might vary, but something like:

select coalesce(product, 'Total'), count(cost) as totalproductcost
from product_Table
where product_Tier = 'grocery' and
      as_of_date = '2020-08-13'
group by product with rollup;
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement