/*Group by, Having and compute Examples Sep 1999 */

/* Start with simple examples */

select pub_id,price*ytd_sales 
from titles
group by pub_id
order by pub_id

/* OK, add both select fields to the group-by clause */

select pub_id,price*ytd_sales 
from titles
group by pub_id, price*ytd_sales
order by pub_id

/* Add an aggregate function */

select pub_id,sum(price*ytd_sales)
from titles
group by pub_id
order by pub_id

/* Do we need the order-by clause? */

select pub_id,sum(price*ytd_sales)
from titles
group by pub_id


/* Add a having clause */

select pub_id,sum(price*ytd_sales)
from titles
group by pub_id
having sum(price*ytd_sales) < 400000

/* Add a where clause */

select pub_id,sum(price*ytd_sales)
from titles
where pub_id > '0800'
group by pub_id
having sum(price*ytd_sales) < 400000

/* Can we get same result with just a where clause */

select pub_id,sum(price*ytd_sales)
from titles
where pub_id > '0800' and sum(price*ytd_sales) < 400000
group by pub_id

/* Use the All keyword */

select pub_id,sum(price*ytd_sales)
from titles
where pub_id > '0800'
group by pub_id

select pub_id,sum(price*ytd_sales)
from titles
where pub_id > '0800'
group by all pub_id

/* Get average  price of our products */

use belldemo
go
select avg(curPprice) from tblProduct

/* Double the price and get the average */

select avg(2*curPprice) from tblProduct

use pubs
go
select type, avg(price)
from titles
group by type


select type, avg(price)
from titles
group by type
order by avg(price)

/* Now more advanced examples */

select stor_id,qty   
 from sales  
 where stor_id < '7100'   /* Note have 8 rows */

/* The following works but gives erroneous information - we lost a row */
/* Group by forces one row for each group - here it combined qty of 20 rows */

select stor_id, qty
from sales
where stor_id < '7100'
group by stor_id, qty

/* Following is the normal use of Group-By (Use aggregate function) */

select stor_id, sum(qty) from sales
where stor_id < '7100'
group by stor_id
order by stor_id

/* Add a having clause to restrict the summary rows */

select stor_id, sum(qty) from sales
where stor_id < '7100'
group by stor_id
having sum(qty) > 10
order by stor_id

/*Remove the Having clause and add a column (title_id) */
/* Get a result like one we saw before, so why use this? */

select stor_id, title_id, sum(qty) from sales
where stor_id < '7100'
group by stor_id, title_id
order by stor_id

/* Get useful output if we had multiple sales orders */
/* for the same book from the same store */

/* First, make a table to play with */

select * into sales1
from sales

sp_dboption pubs, 'select', true

select * into sales1		
from sales

select stor_id, title_id, qty from sales1
where stor_id < '7100'

/* Add a sale */

insert into sales1
values ('7066', 'A2977', '05/25/87', 6, 'Net 60', 'PC8888')

/* Run the same query. Note that store 7066 selling pc8888 now has 56 */
/* So, two group-by columns does make sense */

select stor_id, title_id, sum(qty) from sales1
where stor_id < '7100'
group by stor_id, title_id

/* Review original good query to remind ourselves of its output */
/* 9 rows now */

select stor_id,qty  
 from sales1  
 where stor_id < '7100'

/* Add a compute clause */

select stor_id,qty  
 from sales  
where stor_id < '7100'
order by stor_id
compute sum(qty)

/* Add 'by' to compute clause */

select stor_id,qty  
 from sales  
where stor_id < '7100'
order by stor_id
compute sum(qty) by stor_id

/* Now combine the compute alternatives */

select stor_id,qty  
 from sales  
where stor_id < '7100'
order by stor_id
compute sum(qty) by stor_id
compute sum(qty)