How to add a subtotal row in sql
In general, it would be something like this:
select Name, Score
from (
select Name, Score, Name as o
from Table1 as a
union all
select 'Subtotal', sum(Score), Name as o
from Table1 as a
group by Name
) as a
order by o, score
But, just as @ypercube said, there're different specific implementations in different RDBMS. Your task a bit complicated, because your table doesn't have primary key, so you can emulate it with row_number() function. For SQL Server you can use grouping sets:
with cte as (
select *, row_number() over(order by newid()) as rn
from Table1
)
select
case
when grouping(c.rn) = 1 then 'Subtotal'
else c.Name
end as Name,
sum(c.Score) as Score
from cte as c
group by grouping sets ((c.Name), (c.Name, c.rn))
order by c.Name;
Or rollup():
with cte as (
select *, row_number() over(order by newid()) as rn
from Table1
)
select
case
when grouping(c.rn) = 1 then 'Subtotal'
else c.Name
end as Name,
sum(c.Score) as Score
from cte as c
group by rollup(c.Name, c.rn)
having grouping(c.Name) = 0
order by c.Name;
Note the grouping() function to replace name column for static 'Subtotal' string. Also note, that order of columns matters in rollup() query.
=> sql fiddle demo
SQL query: how to create subtotal rows when there is no aggregate function
One option is Grouping Sets
Example
Declare @YourTable Table ([Date] varchar(50),[INVNUNBER] varchar(50),[CUSTOMER] varchar(50),[ITEM] varchar(50),[QTY] int,[SALES] int)
Insert Into @YourTable Values
(20190630,'IN3343','joe''s comp',23225,2.0,3000)
,(20190630,'IN3343','joe''s comp',23214,1.0,400)
,(20190630,'IN3353','matt''s comp.',12222,3.0,6000)
,(20190630,'IN3353','matt''s comp.',32222,3.0,3000)
Select Date
,InvNunber
,Customer
,Item
,Qty = sum(Qty)
,Sales = sum(Sales)
From @YourTable
Group By
Grouping Sets (
(Date,InvNunber,Customer,Item)
,(Date,InvNunber)
,(left(Date,0))
)
Order By left(Date,0) Desc
,Date
,InvNunber
,Customer Desc
Returns
Date InvNunber Customer Item Qty Sales
20190630 IN3343 joe's comp 23214 1 400
20190630 IN3343 joe's comp 23225 2 3000
20190630 IN3343 NULL NULL 3 3400
20190630 IN3353 matt's comp. 12222 3 6000
20190630 IN3353 matt's comp. 32222 3 3000
20190630 IN3353 NULL NULL 6 9000
NULL NULL NULL NULL 9 12400
SQL SERVER T-SQL Calculate SubTotal and Total by group
I think this is what you want:
select (case when GROUPING(CustomerName) = 0 and
GROUPING(Employee) = 1 and
GROUPING(DocDate) = 1 and
GROUPING(LegalID) = 1
then 'Total ' + CustomerName
when GROUPING(CustomerName) = 1 and
GROUPING(Employee) = 1 and
GROUPING(DocDate) =1 and
GROUPING(LegalID) = 1 then 'Total'
else CustomerName
end) as CustomerName,
LegalID, Employee,DocDate,
sum(DocTotal) as DocTotal,
sum(DueTotal) as DueTotal
From @Sales
group by grouping sets((LegalID, CustomerName ,Employee, DocDate),
(CustomerName),
()
);
sql add subtotal and grand total
The issue is a) your grouping sets aren't correct, and b) you don't have any aggregate functions to do the grouping sets against.
I think the following is what you're after:
WITH your_results AS (SELECT 'Information System' prog_name, 'Jiang, Yaohan' st_name, 10 loan, 'Cyert Hall, 0701' LOCATION, 0 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Jiang, Yaohan' st_name, 12 loan, 'Cyert Hall, 0701' LOCATION, 2 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Jiang, Yaohan' st_name, 13 loan, 'Cyert Hall, 0701' LOCATION, 6 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Jiang, Yaohan' st_name, 14 loan, 'Tepper Quad, 1009' LOCATION, 7 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Jiang, Yaohan' st_name, 16 loan, 'Warner Hall, 1304' LOCATION, 7 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Xiao, Shan' st_name, 7 loan, 'Cyert Hall, 0701' LOCATION, 9 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Xu, Sheng' st_name, 1 loan, 'Baker Building, 1101' LOCATION, 11 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Xu, Sheng' st_name, 6 loan, 'Porter Hall, 1004' LOCATION, 9 weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Ouyang, Hsuan' st_name, 4 loan, 'Baker Building, 1101' LOCATION, 1 weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Ouyang, Hsuan' st_name, 8 loan, 'Tepper Quad' LOCATION, 5 weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Peng, Bo' st_name, 3 loan, 'Warner Hall, 1304' LOCATION, 1 weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Peng, Bo' st_name, 15 loan, 'Warner Hall, 1304' LOCATION, NULL weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Wu, Shinyu' st_name, 2 loan, 'Tepper Quad' LOCATION, 4 weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Wu, Shinyu' st_name, 5 loan, 'Tepper Quad' LOCATION, 0 weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Yin, Abby' st_name, 9 loan, 'Tepper Quad' LOCATION, 1 weeks FROM dual)
SELECT prog_name,
st_name,
sum(loan) loan,
LOCATION,
sum(weeks) weeks
FROM your_results
GROUP BY GROUPING SETS ((prog_name, st_name, loan, location, weeks), (prog_name), ())
ORDER BY prog_name, st_name, weeks;
PROG_NAME ST_NAME LOAN LOCATION WEEKS
---------------------- ------------- ---------- -------------------- ----------
Information System Jiang, Yaohan 10 Cyert Hall, 0701 0
Information System Jiang, Yaohan 12 Cyert Hall, 0701 2
Information System Jiang, Yaohan 13 Cyert Hall, 0701 6
Information System Jiang, Yaohan 14 Tepper Quad, 1009 7
Information System Jiang, Yaohan 16 Warner Hall, 1304 7
Information System Xiao, Shan 7 Cyert Hall, 0701 9
Information System Xu, Sheng 6 Porter Hall, 1004 9
Information System Xu, Sheng 1 Baker Building, 1101 11
Information System 79 51
Information Technology Ouyang, Hsuan 4 Baker Building, 1101 1
Information Technology Ouyang, Hsuan 8 Tepper Quad, 1009 5
Information Technology Peng, Bo 3 Warner Hall, 1304 1
Information Technology Peng, Bo 15 Warner Hall, 1304
Information Technology Wu, Shinyu 5 Tepper Quad, 1009 0
Information Technology Wu, Shinyu 2 Tepper Quad, 1009 4
Information Technology Yin, Abby 9 Tepper Quad, 1009 1
Information Technology 46 12
125 63
(You would replace the your_results subquery with the query that returns the data you're trying to group against.)
This has the advantage of not requiring SQL*Plus features (break, compute).
If you still only want to output the prog_name for the first row without using SQL*Plus features, you would do:
SELECT CASE WHEN rn = 1 THEN pn END prog_name,
st_name,
loan,
LOCATION,
weeks
FROM (SELECT prog_name pn,
st_name,
sum(loan) loan,
LOCATION,
sum(weeks) weeks,
row_number() OVER (PARTITION BY prog_name ORDER BY st_name, weeks, LOCATION) rn
FROM your_results
GROUP BY GROUPING SETS ((prog_name, st_name, loan, location, weeks), (prog_name), ()))
ORDER BY pn, st_name, weeks, LOCATION;
The right way to calculate subtotals using SQL
I would use grouping sets (see here):
select Person#, Name, Gender, sum(networth) as networth
from t
group by grouping sets ( (#Person, Name, Gender), (Gender));
Although this appears to do an extra aggregation, the syntax is flexible for all sorts of subtotals. For instance, if you wanted the overall total as well:
group by grouping sets ( (#Person, Name, Gender), (Gender), ());
Add a summary row with totals
If you are on SQL Server 2008 or later version, you can use the ROLLUP() GROUP BY function:
SELECT
Type = ISNULL(Type, 'Total'),
TotalSales = SUM(TotalSales)
FROM atable
GROUP BY ROLLUP(Type)
;
This assumes that the Type column cannot have NULLs and so the NULL in this query would indicate the rollup row, the one with the grand total. However, if the Type column can have NULLs of its own, the more proper type of accounting for the total row would be like in @Declan_K's answer, i.e. using the GROUPING() function:
SELECT
Type = CASE GROUPING(Type) WHEN 1 THEN 'Total' ELSE Type END,
TotalSales = SUM(TotalSales)
FROM atable
GROUP BY ROLLUP(Type)
;
how to use subtotal in SQL
SELECT Code,
isNULL(NAME,'SUBTOTAL') AS NAME,
SUM(Total) AS Total,
SUM(Vat) AS Vat,
SUM(TotalAmt) AS
TotalAmt,
Flag
FROM TABLE GROUP BY ROLLUP(Flag,Code,Name);
SQL query to get the subtotal of some rows
You can aggregate using a CASE expression, which forms a group using the id if the manager_id be zero, otherwise using the manager_id. The rest of the logic is similar to what you already have.
SELECT
CASE WHEN manager_id = 0 THEN id ELSE manager_id END AS manager_id,
MAX(CASE WHEN is_manager=1 THEN name END) AS name,
SUM(no_of_items) AS total_items,
SUM(revenue) AS total_revenue
FROM items_revenue
GROUP BY
CASE WHEN manager_id = 0 THEN id ELSE manager_id END;
Demo
One side note: I used a function in the GROUP BY clause, which is not ANSI compliant and therefore may not run everywhere. To fix this, we can first subquery your table to generate the effective manager groups. Then, use my above answer against this intermediate result.
Related Topics
How to Execute a Stored Procedure Inside a Select Query
Select into with More Than One Attribution
Split String by Space and Character as Delimiter in Oracle with Regexp_Substr
Returning Result Even for Elements in In List That Don't Exist in Table
How to Create Temp Table with Select * into Temptable from Cte Query
SQL Server - Where Is "Sys.Functions"
SQL Query to Get the Deadlocks in SQL Server 2008
How to Insert into a Table with Just One Identity Column
How to Return Default Value from SQL Query
Executing SQL Server Agent Job from a Stored Procedure and Returning Job Result
How to Create an Index for Elements of an Array in Postgresql
How to Find Out What Foreign Key Constraint References a Table in SQL Server