CODEWITHSUNDEEP
sqlsql-server
Arthur Hainline
Arthur Hainline

Reputation: 13

comparison of joins

Say I have a table with all sales called sales with columns itemid, storeid, sale, and date

I then have a table called storeregion that has the columns storeid and region. If I want to get the sales in a region for a specific date range: 

select region, sum(sale)

from sales s
inner join storeregion sr on s.storeid=sr.storeid

where date between 'whatever' and 'whatever'

group by region

So I get a result like this:

East|500

West|400

OK cool. Now, I also have a table called itemcategory with columns itemid and category. I want to see the sales of each category in each region. I can do something like this. 

select sr.region, ic.category, sum(sale)

from sales s
inner join storeregion sr on s.storeid=sr.storeid
inner join itemcategory ic on s.itemid=ic.itemid

where date between 'whatever' and 'whatever'

group by sr.region
group by ic.category

So I get a result like this:

East|Toys|100

East|Books|200

East|Games|200

West|Toys|300

West|Games|100

Now what I really want to do is find where the sales of one category in one region is more than 50% of the total sales in that same region. So as per my example in the first query I get the result:

West|400

and in the second query I get the result:

West|Toys|300

which is greater than 50% of the total sales in the region.

I want to write one query that will only give me the result

West|Toys|300

because it is more than 50% of the sales in the region. Any ideas?

Upvotes: 1

Views: 71

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You should do this using window functions:

select rc.*
from (select sr.region, ic.category, sum(sale) as catsale,
             sum(sum(sale)) over (partition by region) as regsale
      from sales s inner join
           storeregion sr
           on s.storeid = sr.storeid inner join
           itemcategory ic
           on s.itemid = ic.itemid
      where date between 'whatever' and 'whatever'
      group by sr.region, ic.category
     ) rc
where catsale >= 0.5 * regsale;

In general, window functions not only result in shorter queries, but they also perform better than the equivalent queries using multiple joins and aggregations.

Upvotes: 1

D Stanley
D Stanley

Reputation: 152521

You do that with a subquery. join your query to a subquery that calculates the total by region, with a condition that sales > 50% of that total:

select sr.region, ic.category, sum(sale) sales

from 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

    from sales s
    inner join storeregion sr on s.storeid=sr.storeid

    where date between 'whatever' and 'whatever'

    group by region

) st ON sr.region = st.region
where date between 'whatever' and 'whatever'

group by sr.region,ic.category, st.sales
having sum(sale) > st.sales * 0.50

Note that the condition needs to be in a HAVING clause since it applies to the group total, not a record value.

Upvotes: 3

Beto
Beto

Reputation: 333

You will need to do something like:

select a.region, a.category, a.sales from (
 your first query
) as a join (
your second query
) as b on join both
having b.sales > a.sales / 2

Upvotes: 0

Related Questions