asked on

Help with sum in SQL Subqueries

I'm trying to get the sum value of allocated as per code below but only the sum for the current product not all products. I can do this in a single query but only by using the group by which returns a not allowed when in a subquery.

I then need to add more subqueries for allocated quantities for Tuesday, Weds etc.

Hope you can help.

select 
Aisle,
Bay,
dept_code+base_code+colour+size as ThePartNo,
description,
full_plu.wh_stock,
sum(allocated) as Allocated,
 
(select sum(allocated) from branch_stock 
where EXISTS(select BranchCode from PickRoutes where BranchCode = branch_stock.branch and RouteNumber = 3)) as Monday
 
from branch_stock
INNER JOIN full_plu on branch_stock.dept_code+base_code+colour+size = full_plu.dp+st+col+sz
where Aisle = 'I1'
group by 
dept_code+base_code+colour+size,
description,
Aisle,
Bay,
full_plu.dp+st+col+sz,
full_plu.wh_stock
order by ThePartNo

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

andrewmilner

ASKER

Amazing!!! It's about there. Updated code below.
I am however getting an inflated value for the sum(bs.allocated) as Allocated

For example, if I run simply
select sum(allocated) from branch_stock where dept_code+base_code+colour+size = '22265400000'
Then I get 31 which is correct but the big query below gives 120
Running this on other partno's does the same but the percentage increase is different each time...

Correct Value from Short Query Bloated value from big query
------------------------------------------ ---------------------------------------------
31 120
4 16
12 30

Any ideas?

Many Thanks,

select 
  bs.Aisle
, bs.Bay + bs.Shelf as BayShelf
, bs.dept_code+bs.base_code+bs.colour+bs.size as ThePartno
, bs.description
, fp.act_wh_stock
, sum(bs.allocated) as Allocated
, sum(case when pr.routenumber = 3 then bs.allocated end) Monday
, sum(case when pr.routenumber = 4 then bs.allocated end) Tuesday
, sum(case when pr.routenumber = 5 then bs.allocated end) Wednesday
, sum(case when pr.routenumber = 6 then bs.allocated end) Thursday
, sum(case when pr.routenumber = 1 then bs.allocated end) Friday
from branch_stock bs
JOIN full_plu fp
  on bs.dept_code = fp.dp
 and bs.base_code = fp.st
 and bs.colour = fp.col
 and bs.size = fp.sz
left join PickRoutes pr
  on pr.branchcode = bs.branch
where bs.Aisle = 'I1'
group by bs.dept_code
, bs.base_code
, bs.colour
, bs.size
, bs.description
, bs.Aisle
, bs.Bay+bs.Shelf
, fp.dp
, fp.st
, fp.col
, fp.sz
, fp.act_wh_stock

Open in new window

andrewmilner

ASKER

If I add a group by bs.Allocated at the bottom then it shows multiple rows for each product and the total of the allocated values with this grouping in is what is being summed without the grouping.

I can acheive correct values if I take out the sum(bs.allocated) as allocated and replace with....
(select sum(allocated) from branch_stock where dept_code+base_code+colour+size = bs.dept_code+bs.base_code+bs.colour+bs.size)

But it makes the query time really slow.

andrewmilner

ASKER

Query time 6 mins for full report!!! agh! - there are however 200,000+ records in branch_stock.

Any ideas?
Without the sub query in there fore the total allocated then its rapid.

andrewmilner

ASKER

Continued - https://www.experts-exchange.com/questions/24335299/Help-with-SQL-Query.html