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
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), ());
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),
()
);
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 How to Query Total & Subtotal
Use case and sum:
select day,
count(1) as total_order
sum(case when order_type='offline' then 1 end) as num_offline_order,
sum(case when order_type='online' then 1 end) as num_online_order
from sample_table
group by day
order by day
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 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;
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.
Add a subtotal column for aggregated columns
You seem to want:
SELECT OT.TRADER_ID, OT.TRADER_NAME, OT.CCP,
COUNT(*) OVER (PARTITION BY OT.TRADER_ID) as NUM_CCP,
SUM(OT.TRADED_AMT) AS TRADED_AMT_WITH_CCP,
SUM(SUM(OT.TRADED_AMT)) OVER (PARTITION BY OT.TRADER_ID) AS VALUE_OF_TOTAL_TRADES,
COUNT(OT.TRADE_ID) AS CCP_TRADES,
SUM(COUNT(OT.TRADE_ID)) OVER (PARTITION BY OT.TRADER_ID) AS TOTAL_TRADES
FROM ORDERS_TRADES OT
GROUP BY OT.TRADER_ID, OT.TRADER_NAME, OT.CCP;
I'm not sure what your query has to do with the results you want. The columns have little to do with what you are asking.
Here is a db<>fiddle.
Related Topics
Find the Date/Time a Table's Column Was Created
Linq to SQL "Not Like" Operator
Does SQL Server Optimize Dateadd Calculation in Select Query
MySQL Count() Multiple Columns
Datename(Month,Getadate()) Is Returning Numeric Value of the Month as '09'
Sql: Parse Comma-Delimited String and Use as Join
Is This Normalization Correct? (Two Many-To-Manys Connected by a Many-To-One)
Insert Blank Row Between Groups of Rows and Sorted by Id in SQL
Getting Unavailable Dates for Renting a Product That Has Stocks
Generally, Are String (Or Varchar) Fields Used as Join Fields
How to Select a Column in SQL Server with a Special Character in the Column Name
SQL Query to Get Recursive Count of Employees Under Each Manager
Free Space in MySQL After Deleting Tables & Columns
SQL Server Normalization Tactic: Varchar VS Int Identity
How to Optimize This SQL Query (Using Indexes)
How to Get a Value Using SQL in Delphi and Setting the Value to a Variable