9 Replies - 579 Views - Last Post: 08 April 2013 - 06:59 AM Rate Topic: -----

#1 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Sql Query Problem grouping and where clause

Posted 05 April 2013 - 08:49 AM

 "SELECT     Item_ID, SUM(Stock_Quantity) AS Quantity, Stock_Type, Batch_No FROM(dbo.Stock_Header)  WHERE Item_ID =1 GROUP BY Item_ID, Stock_Type, Batch_No"")"



In my table im purchasing medicine from shops ... and selling to people..here is the format of my table

Item_ID Quantity Stock_Type
1 12 Stock IN
2 3 Stock IN
1 5 Stock OUT

NOW in item id=1 i have purchased 12 cards and sold 5 card..is there a way where i can find the query which will give me the result of how much stock i left now in item_id 1 . 12-5=7.

Is This A Good Question/Topic? 0
  • +

Replies To: Sql Query Problem grouping and where clause

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,462
  • Joined: 12-June 08

Re: Sql Query Problem grouping and where clause

Posted 05 April 2013 - 09:24 AM

You are not thinking big enough.. you'll need to sum up (per grouping by the id) each section.. so one for 'in' and one for 'out'.. then use a full outer join to get both together and do the subtraction!


create table  #foo (ID int, order_val int, order_type varchar(3))
insert into #foo (id, order_val, order_type)
values(1, 12, 'IN')

insert into #foo (id, order_val, order_type)
values(2, 3, 'IN')

insert into #foo (id, order_val, order_type)
values(1, 5, 'OUT')

insert into #foo (id, order_val, order_type)
values(3, 7, 'OUT')

select *
from #foo

-- the final product.. if no id in the first subquery then use the id in the second sub query.
-- just subtract the in from the out and you have your answer!
select isnull(a.ID, b.id) as id, (isnull(a.order_val, 0) - isnull(b.order_val, 0)) as stock_left 
from -- we can treat this inner select statement like a table!
(
	-- collect all the 'in' values, and sum them up per their id.
	select Id, sum(order_val) as order_val
	from #foo
	where order_type = 'IN'
	group by Id
) as a -- gotta give it a name though
full outer join -- since there maybe cases where the id is only in the 'out' and not in the 'in' use the full outer join to hit all the rows from both tables.
(
	-- collect all the 'out' per the order type
	select Id, sum(order_val) as order_val
	from #foo
	where order_type = 'OUT'
	group by Id
) as b
on a.id = b.id -- join condition. 
 


drop table  #foo 



id   order_val   order_type
1	12	IN
2	3	IN
1	5	OUT
3	7	OUT


id   stock
1	7
2	3
3	-7

Was This Post Helpful? 1
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Sql Query Problem grouping and where clause

Posted 05 April 2013 - 02:14 PM

I think he wasn't far off with his first post, just needed a switch on the stock type:
SELECT
	Item_ID,
	SUM(CASE
		WHEN Stock_Type = 'IN' THEN 1
		ELSE -1
	END * Stock_Quantity) AS Quantity,
	Stock_Type
FROM Stock_Header
GROUP BY
	Item_ID,
	Stock_Type


Was This Post Helpful? 0
  • +
  • -

#4 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Sql Query Problem grouping and where clause

Posted 06 April 2013 - 07:40 AM

View Postmodi123_1, on 05 April 2013 - 09:24 AM, said:

You are not thinking big enough.. you'll need to sum up (per grouping by the id) each section.. so one for 'in' and one for 'out'.. then use a full outer join to get both together and do the subtraction!


create table  #foo (ID int, order_val int, order_type varchar(3))
insert into #foo (id, order_val, order_type)
values(1, 12, 'IN')

insert into #foo (id, order_val, order_type)
values(2, 3, 'IN')

insert into #foo (id, order_val, order_type)
values(1, 5, 'OUT')

insert into #foo (id, order_val, order_type)
values(3, 7, 'OUT')

select *
from #foo

-- the final product.. if no id in the first subquery then use the id in the second sub query.
-- just subtract the in from the out and you have your answer!
select isnull(a.ID, b.id) as id, (isnull(a.order_val, 0) - isnull(b.order_val, 0)) as stock_left 
from -- we can treat this inner select statement like a table!
(
	-- collect all the 'in' values, and sum them up per their id.
	select Id, sum(order_val) as order_val
	from #foo
	where order_type = 'IN'
	group by Id
) as a -- gotta give it a name though
full outer join -- since there maybe cases where the id is only in the 'out' and not in the 'in' use the full outer join to hit all the rows from both tables.
(
	-- collect all the 'out' per the order type
	select Id, sum(order_val) as order_val
	from #foo
	where order_type = 'OUT'
	group by Id
) as b
on a.id = b.id -- join condition. 
 


drop table  #foo 



id   order_val   order_type
1	12	IN
2	3	IN
1	5	OUT
3	7	OUT


id   stock
1	7
2	3
3	-7

as u told i tried your code but im getting error stock_quantity is invalid column name..i have attached snap shot of my error..im doing in vb.net .i tried this query in sql also and getting same error
select isnull(a.item_id , b.item_id) as id , (isnull(a.stock_quantity,0)-isnull(b.stock_quantity,0)) as stock_left from(select item_id , sum(stock_quantity) as stock from stock_header where stock_type='stock in' group by item_id ) as a full outer join(select item_id,sum(stock_quantity) as order_val from stock_header where stock_type='stock out' group by item_id) as b on a.item_id=b.item_id



e_i_pi thnx for help i will surely work on that .but right now i modi query is according to my needs

Attached image(s)

  • Attached Image

This post has been edited by vks.gautam1: 06 April 2013 - 07:50 AM

Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,462
  • Joined: 12-June 08

Re: Sql Query Problem grouping and where clause

Posted 06 April 2013 - 08:07 AM

You didn't create the example table the example needs.
Was This Post Helpful? 0
  • +
  • -

#6 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Sql Query Problem grouping and where clause

Posted 06 April 2013 - 08:49 AM

View Postmodi123_1, on 06 April 2013 - 08:07 AM, said:

You didn't create the example table the example needs.

select isnull(a.id,b.id) as id ,(isnull(a.order_val,0)-(b.order_val,0)) as stock_left from (select id,sum(order_val) as order_val from foo where order_type='IN' group by id) as a full outer join(select id,sum(order_val) as order_val from foo where order_type='out' group by id)as b on a.id=b.id


im getting the syntax error..pls find the enclosed file of that

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,462
  • Joined: 12-June 08

Re: Sql Query Problem grouping and where clause

Posted 06 April 2013 - 08:58 AM

,(isnull(a.order_val,0)-(b.order_val,0))

is wrong.
Was This Post Helpful? 0
  • +
  • -

#8 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Sql Query Problem grouping and where clause

Posted 08 April 2013 - 05:14 AM

View Postmodi123_1, on 06 April 2013 - 08:58 AM, said:

,(isnull(a.order_val,0)-(b.order_val,0))

is wrong.

select isnull(a.id,b.id) as id ,(isnull(a.order_val,0)-isnull(b.order_val,0)) as stock_left from (select id,sum(order_val) as order_val from foo where order_type='IN' group by id) as a full outer join(select id,sum(order_val) as order_val from foo where order_type='out' group by id)as b on a.id=b.id


Msg 208, Level 16, State 1, Line 1
Invalid object name 'foo'.
i have tried it is giving me same error saying invalid object name foo..
saying table is invalid
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,462
  • Joined: 12-June 08

Re: Sql Query Problem grouping and where clause

Posted 08 April 2013 - 06:46 AM

For the love of GIJOE... really? 'foo' is the temp table I created. If you are having issues replacing a table name with something you created then it may be a high time to reevaluate your SQL learn'n up to now. Just say'n.
Was This Post Helpful? 0
  • +
  • -

#10 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Sql Query Problem grouping and where clause

Posted 08 April 2013 - 06:59 AM

View Postmodi123_1, on 08 April 2013 - 06:46 AM, said:

For the love of GIJOE... really? 'foo' is the temp table I created. If you are having issues replacing a table name with something you created then it may be a high time to reevaluate your SQL learn'n up to now. Just say'n.

i have made the table foo and i have worked on my existing table also ..im getting same error..
simple queries are working on these tables but not this query..


lol now the same query is working..hmmm let me check all again thanks

This post has been edited by vks.gautam1: 08 April 2013 - 07:12 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1