CODEWITHSUNDEEP
sqldb2
Joey112
Joey112

Reputation: 25

SQL join with aggregate function

I've been asked to start working more with data analysis and have ran into similar issues with SQL aggregate functions. Here's a very simplified dataset of sales transaction log and warehouse balance:

SALES
Item    Period
A        2019
A        2019
A        2018
B        2019

WAREHOUSE
Item     SerialN
A        111
A        123
A        222
C        321

I need to run SQL COUNT function on SALES table which is simple enough:

select count(a.Item), a.Item from SALES a
where a.period = 2019
group by a.Item

I also need to add the COUNT of items in WAREHOUSE table - also very straightforward on its own but not so much when trying to combine them into one query result.

select count(a.Item), a.Item, count(b.Item) from SALES a
left join WAREHOUSE b on a.Item = b.Item

where a.period = 2019
group by a.Item

The one above would mess up the result due to JOIN multiplying effect from WAREHOUSE table. What would be the best way to reach the result below? Is there a neat way to do this at all?

Item    count_period    current_warehouse
A            2                 3
B            1

Upvotes: 2

Views: 7975

Answers (2)

VBoka
VBoka

Reputation: 9083

First you need to group and filter data from the first table and then join with second table. Like this:

select A.item, A.count_period, count(W.item) current_warehouse
from (select S.item, max(S.Period) Period, count(S.Period) count_period    
      from SALES S
      where S.Period = 2019
      group by S.item) A
left join WAREHOUSE W on A.Item = W.Item
group by A.item, A.count_period;

One more way is:

select A.item, A.count_period, B.current_warehouse from 
(select S.item, max(S.Period) Period, count(S.Period) count_period    
      from SALES S
      where S.Period = 2019
      group by S.item) A
left join       
(select W.item, count(W.item) current_warehouse
from WAREHOUSE W
group by W.item) B
on A.item = B.item

or

select A.item, max(A.count_period), count(W.item) current_warehouse
from (select S.item, max(S.Period) Period, count(S.Period) count_period    
      from SALES S
      where S.Period = 2019
      group by S.item) A
left join WAREHOUSE W on A.Item = W.Item
group by A.item;

Here you have a DEMO where you can see all three are returning same data.

Upvotes: 1

Parfait
Parfait

Reputation: 107652

Consider joining two aggregate queries using Common Table Expressions (CTE):

WITH s_agg AS (
   select s.Item, count(s.Item) as sales_count 
   from SALES s
   where s.period = 2019
   group by s.Item
), 
   w_agg as (
   select w.Item, count(w.Item) as warehouse_count
   from WAREHOUSE w
   group by w.Item
)

SELECT s_agg.Item, 
       s_agg.sales_count,
       w_agg.warehouse_count
FROM s_agg
LEFT JOIN w_agg
  ON s_agg.Item = w_agg.Item

Upvotes: 1

Related Questions