CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlselectjoin
response.write
response.write

Reputation: 175

SUM from Select query then Show as another Column

I have this table that hold several products

enter image description here

And another table that holds the order for each product from above

enter image description here

How can i combine these tables and get the sum of each ord_Count per item and show it as another column, like this

enter image description here

To summarize it, I have a Items table that holds different products, and an Orders table that holds the orders for each product from the Items Table, then I want to have a query that combines both tables and show a my stock for each item from the Items table.

Upvotes: 0

Views: 55

Answers (2)

mohan111
mohan111

Reputation: 8865

declare @t table (item varchar(20),itemcode varchar(20))
insert into @t (item,itemcode)values ('choco','ckschoc'),('chocowafer','wfrchoc'),('chocostrrae','wfrstr')
declare @tt table (ordcnt int,itemcode varchar(20),dated date)
insert into @tt (ordcnt,itemcode,dated)values (20,'ckschoc','4/24/2015'),(10,'wfrchoc','4/24/2015'),(15,'wfrstr','4/24/2015')
,(30,'ckschoc','4/24/2015'),(20,'wfrstr','4/24/2015')
we can achieve the same result using sub query and corelated join query also

Solution 

select t.itemcode,p.S  from @t t INNER JOIN   (
select itemcode,SUM(ordcnt)S from @tt
GROUP BY  itemcode)P
ON p.itemcode = t.itemcode
group by  t.itemcode,P.s

select t.itemcode,
(select SUM(tt.ordcnt)Cnt from @tt tt 
where tt.itemcode = t.itemcode 
group by tt.itemcode )Cnt from @t t

Upvotes: 0

dario
dario

Reputation: 5259

Try this:

SELECT SUM(ord_count) AS Item_stock, itm_Code
FROM YourTable
GROUP BY itm_Code

To combine both table:

SELECT SUM(B.ord_count) AS Item_stock, B.itm_Code
FROM YourTable1 AS A
INNER JOIN YourTable2 AS B
    ON A.itm_Code = B.itm_Code
GROUP BY B.itm_Code

Upvotes: 3

Related Questions