CODEWITHSUNDEEP
sqljoinleft-joininner-join
louise
louise

Reputation: 23

Joining 2 tables error

I'm trying to join 2 tables to get an output report. The tables involved are the stock and dailysales table.

Stock and Dailysales tables:

enter image description here

Desired output format:

enter image description here

I am trying to join 2 tables by using the below query

Select item,article,sold,stockonhand
from stock S
left join dailysales as D on S.item=D.item
group by item

I want the output to include all rows from the stock table. Like there is stock, but not sold, also to be included in the report. Currently my report is does not show, (stocked but not sold).

Hope you can understand my context or point me to a tutorial which I could read up. I tried to search for few days and couldn't find an exact question to mine.

Upvotes: 1

Views: 84

Answers (3)

Eric
Eric

Reputation: 5733

You can sum the item in a separated table, it would be clearer than GROUP BY 2 tables

SELECT 
    s.item, 
    s.article, 
    ISNULL(ds.sold, 0) AS qtysold, 
    s.stockonhand
FROM 
    stock s OUTER APPLY 
    (SELECT SUM(sold) AS sold FROM dailysales WHERE item = s.item GROUP BY item) ds

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Simply LEFT JOIN the two tables (to get the flute too), do a GROUP BY, and SUM the sold:

select s.item, s.article, coalesce(SUM(d.sold),0) as "qty sold", s.stockonhand
from stock S
left join dailysales as D on S.item=D.item
group by s.item, s.article, s.stockonhand

The coalesce is there to replace NULL with 0, for items not sold. (Thanks sagi!)

General GROUP BY tip: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function.

Also, you can remove the article column from the dailysales table. That data is already stored in the stock table. Never store same data twice! (Normalization!) Risk for data inconsistency if you keep that column.

Upvotes: 1

sagi
sagi

Reputation: 40481

Not tested -

select item,article,sum(sold),sum(stockonhand)-sum(sold) from (
 select a.item,a.article,a.stockonhand,case when b.sold is null then 0 else b.sold end as sold 
 from stock a left join dailysales b on (a.item = b.item))
group by item,article;

It's basically does the left join and put 0 on the null column(for sum after) and then summing up the results grouping by all the columns in the select(that what was wrong with your query)

Upvotes: 2

Related Questions