sql - comparison of joins -

say have table sales called sales columns itemid, storeid, sale, , date

i have table called storeregion has columns storeid , region. if want sales in region specific date range:

select region, sum(sale)  sales s inner join storeregion sr on s.storeid=sr.storeid  date between 'whatever' , 'whatever'  group region 

so result this:

east|500

west|400

ok cool. now, have table called itemcategory columns itemid , category. want see sales of each category in each region. can this.

select sr.region, ic.category, sum(sale)  sales s inner join storeregion sr on s.storeid=sr.storeid inner join itemcategory ic on s.itemid=ic.itemid  date between 'whatever' , 'whatever'  group sr.region group ic.category 

so result this:

east|toys|100

east|books|200

east|games|200

west|toys|300

west|games|100

now want find sales of 1 category in 1 region more 50% of total sales in same region. per example in first query result:

west|400

and in second query result:

west|toys|300

which greater 50% of total sales in region.

i want write 1 query give me result

west|toys|300

because more 50% of sales in region. ideas?

you subquery. join query subquery calculates total region, condition sales > 50% of total:

select sr.region, ic.category, sum(sale) sales  sales s inner join storeregion sr on s.storeid=sr.storeid inner join itemcategory ic on s.itemid=ic.itemid inner join  (      select region, sum(sale) sales      sales s     inner join storeregion sr on s.storeid=sr.storeid      date between 'whatever' , 'whatever'      group region  ) st on sr.region = st.region date between 'whatever' , 'whatever'  group sr.region,ic.category, st.sales having sum(sale) > st.sales * 0.50 

note condition needs in having clause since applies group total, not record value.