Sql gruoup by query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sql gruoup by query help

Author

Topic

tech426
Starting Member

8 Posts

Posted - 2009-05-26 : 00:45:44

Hi..

i am doing a vb.net application.

i have a table voucher. The columns are voucherno,purpose,amnt. In purpose column the values are mainly fuel,provisions,milk,misc,cash sale.

I want to find sum(amnt) for each purpose. ie, sum(amnt) of fuel, sum(amnt) of milk, sum(amnout) of provisions etc,

584 milk 500
585 proisions 600
851 fuel 500
852 milk 600
862 fuel 200
625 provisions 600

result should be

milk 1100
provisions 1200
fuel 700

when i use the group by query i get the above result. But how can i assign these values to variable and use in th program.

ie if i have a label lblfuel and want to assign the result 700 to lblfuel, how can i do that.

plz help me..

http://imyideas.blogspot.com

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-05-26 : 00:53:15

select
sum(amnt)
,purpose
from table_voucher
group by purpose
-----
with variable try:
--this is sql variable declaration.
declare @purpose varchar(20)
set @purpose = 'milk'

--once you set your variable, you don't need to use group by anymore.
select
sum(ammnt)
from table_voucher
where
purpose = @purpose

All Best

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-26 : 00:54:37

This looks like a vb.net question rather than an sql server question.
In t-sql you would save the resultset in a temp table and work with that.

You can't assign to a variable in one statement as there's more than one value - any you don't know how many there are going to be.

Depends on what you want to do with it. If you just want to display then copy the resultset to a grid control.
If you want variaboles then loop through the resultset and assign each row to entries in an array.

Actually it looks like you have specific fields for the values so you will need to loop through the resultset and check the value of purpose and assign the value to that field.
I would have both field as textboxes so that the purpose and value could both be set from the database (actually I would probably use a control array or grid).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tech426
Starting Member

8 Posts

Posted - 2009-05-26 : 01:02:05

Thanks for your help.

one question. how can i declare the sql variable and use it in the application.

http://imyideas.blogspot.com

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-26 : 01:07:39

Did you understand my post?
Why do you want an sql variable in the app - you are receiving a resultset not a variable.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources