asked on

SQL join ...want to return one row

Hi, I have this query (using SQL server 2005)...

SELECT s.product,s.physical_qty,
      sum(h.val) AS sales
      FROM sysdaa.scheme.stockm s
      left join sysdaa.scheme.opsahistm h
      on s.product = h.product
      WHERE s.physical_qty > 0
      AND h.dated BETWEEN '2017-04-01' AND '2017-05-25'
      AND s.product = 'E-FILL COR975'
      GROUP BY s.product,s.physical_qty;

which returns...

product physical_qty sales
E-FILL COR975 2460 8743.6
E-FILL COR975 8085 8743.6

physical_qty returns 2 results because there are multiple warehouses in sysdaa.scheme.stockm
I tried to sum s.physical_qty but got huge physical qty results and still returned 2 rows

what i need to return is...

product physical_qty sales
E-FILL COR975 10545 8743.6

how can i modify my query to do this? (sum warehouse physical quantities)

Thanks

PortletPaul

SELECT s.product,sum(s.physical_qty) as physical_qty,
sum(h.val) AS sales
FROM sysdaa.scheme.stockm s
INNER join sysdaa.scheme.opsahistm h --inner join!!
on s.product = h.product
WHERE s.physical_qty > 0
AND h.dated BETWEEN '2017-04-01' AND '2017-05-25'
AND s.product = 'E-FILL COR975'
GROUP BY s.product --<< here is the big difference!
;

{+edit} however sum(h.val) will probably increase. you may have to supply some sample data for each table

{+edit 2} because you are filtering by dates in table h, there is no point using a left join to that table

joethermal

ASKER

thanks for the quick response ...that returns one row but physical qty multiplied 23 times and sales multiplied by 2 (which are the number of rows in each table)

product physical_qty sales
E-FILL COR975 242535 17487.2

table contents...
select * from sysdaa.scheme.stockm where product = 'E-FILL COR975'; returns 2 rows each with a different warehouse

select * from sysdaa.scheme.opsahistm
where product = 'E-FILL COR975'
and dated BETWEEN '2017-04-01' AND '2017-05-25'; returns 23 rows of unique invoice numbers (242535 / 23 = 10545)

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

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

Start Free Trial

joethermal

ASKER

Awesome, thanks for your help and advice