Summarize data, Union ALL

Question

Hello everyone,

I have the following query,

SELECT ti.name, sd.qty_ordered, sd.qty_delivered
FROM  salesdetails sd 
INNER JOIN titles ti  ON sd.title_id=ti.title_id Where sd.sonum = 14
Group by ti.name, qty_ordered,sd.qty_delivered

Name	qty_ordered	qty_delivered
Computer	20	20
Emotional	25	25
Life	25	25
Prolonged Data	15	10

I would like to Summarize the totals end of the row. I would  look like this;

Name	qty_ordered	qty_delivered
Computer	20	20
Emotional	25	25
Life	25	25
Prolonged Data	15	10
Totals         85       80

I am reading that i can accomplish that with UNION ALL but not sure how to integrate that into my query.

Thank  you.

---

EverydayLearner

Answer 1

SELECT 
case when GROUPING(ti.name)= 1 THEN 'Total'
ELSE ti.Name END AS Name
, Sum(sd.qty_ordered) as qty_ordered
, SUM(sd.qty_delivered) AS qty_delivered
FROM  salesdetails sd 
INNER JOIN titles ti  
ON sd.title_id=ti.title_id 
Where sd.sonum = 14
Group by ti.name With Rollup

---

Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 2

SELECT ISNULL(ti.name,'Total') name, Sum(sd.qty_ordered) as qty_ordered, SUM(sd.qty_delivered) as qty_delivered
FROM  salesdetails sd 
INNER JOIN titles ti  ON sd.title_id=ti.title_id Where sd.sonum = 14
Group by ti.name
 with rollup


 --Sample
 create table salesdetails (Name	varchar(50),qty_ordered	int,qty_delivered int)
insert into salesdetails values ('Computer',20,20),('Emotional',25,25),('Life',25,25),('Prolonged Data',15,10)

select  ISNULL(Name,'Total') as Name, sum(qty_ordered) as qty_ordered, sum(qty_delivered) as qty_delivered from salesdetails
group by Name
with rollup

drop table salesdetails

Answer 3

SELECT ti.name, sd.qty_ordered, SUM(sd.qty_delivered) AS qty_delivered
FROM  salesdetails sd 
INNER JOIN titles ti  ON sd.title_id=ti.title_id Where sd.sonum = 14
Group by ROLLUP(ti.name, qty_ordered,sd.qty_delivered)
HAVING GROUPING_ID(ti.name, qty_ordered,sd.qty_delivered) IN (0,7)

---

Please Mark This As Answer if it solved your issue
Please Mark This As Helpful if it helps to solve your issue
Visakh
----------------------------
My MSDN Page
My Personal Blog
My Facebook Page

Answer 4

In your first query you have a group by clause but no aggregates.  Is that really your actual query?  Normally one would expect to see the ordered and delivered columns summed.  Without aggregates, the group by clause acts like "distinct" - though you may not realize this without duplicate data (which might only occur rarely and randomly).

However, the general technique would be to add the "total" query to existing one using union all.  E.g. in pseudo-code:

select ... from sd inner join ti on ...
union all
select 'Totals', sum(ordered) , sum(delivered) from sd inner join ti on sd.title_id = ti.title_id and sonum = 14
order by case name when 'Totals' then 1 else 0 end, name;

And one last note.  A resultset has no guaranteed order without an order by clause.  You might assume that your data is ordered when your test data is small and the server is not heavily used - but this is an artifact of usage. There is no guarantee to the order without the order by clause.

Answer 5

create table #t1 (name char(1),c int, c1 int)

insert into #t1 values ('a',10,20)
insert into #t1 values ('b',40,80)


select coalesce(name,'totals') name, sum(c)c,sum(c1) c1
from #t1 
group by name with cube 

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence