i have complex query have dumped temp table.
within data set have different types of deals: movies, cod, features, other.
all deals cod , others , have totalrev_ytd between 10k , 15k should summed single line deal name "total cod" other wise should have it's own line.
how this. can't seem group by using sum(totalrev_ytd) between 10 , 15k.
can please help:
select location, locationid, dealtype, (case when dealtype = 'other (cod, etc)' , totalrev_ytd between 10000 , 15000 'other (cod, etc)' else deal end) deal, rental_pw, rental_mtd, rental_qtd, rental_ytd, sales_pw, sales_mtd, sales_qtd, sales_ytd, otherrev_pw, otherrev_mtd, otherrev_qtd, otherrev_ytd, totalrev_pw, totalrev_mtd, totalrev_qtd, totalrev_ytd #temp_rev t1 dealtype = 'other (cod, etc)' group (case when dealtype = 'other (cod, etc)' , totalrev_ytd between 10000 , 15000 'other (cod, etc)' else deal end), location, locationid, dealtype, rental_pw, rental_mtd, rental_qtd, rental_ytd, sales_pw, sales_mtd, sales_qtd, sales_ytd, otherrev_pw, otherrev_mtd, otherrev_qtd, otherrev_ytd, totalrev_pw, totalrev_mtd, totalrev_qtd, totalrev_ytd an example of have 10 cod, other deals. 1 of deal has totalrev_ytd > 15k. in case should appear own line , other should aggregated.
i think group by good, need on less columns, , aggregate (with sum) numbers should be, well, summed.
in cases "single-line" deal, query sum 1 row, totally ok.
example:
select location, locationid, dealtype , (case when dealtype = 'other (cod, etc)' , totalrev_ytd between 10000 , 15000 'other (cod, etc)' else deal end ) deal, sum(rental_pw) rental_pw, sum(rental_mtd) rental_mtd, ..., -- insert other summed columns here sum(totalrev_ytd) totalrev_ytd #temp_rev t1 dealtype ='other (cod, etc)' group (case when dealtype = 'other (cod, etc)' , totalrev_ytd between 10000 , 15000 'other (cod, etc)' else deal end ) , location, locationid, dealtype 
Comments
Post a Comment