Multiple SQL SUM calculations - 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

Database Design and Application Architecture

Multiple SQL SUM calculations

Author

Topic

ori
Starting Member

1 Post

Posted - 2009-07-31 : 11:05:26

Hi!

I have a big problem.

I have this dBASE tabel and i use TQuery with delphi 2006:

Name Quant_in Val_in Quant_out Val_out
Art1 10 100 5 50
Art2 100 1000 50 500
Art1 100 1000 50 500

I want a result query like this:

Name Quant_in Val_in Quant_out Val_out Qnt_final Val_final
Art1 110 1100 55 550 55 550
Art2 100 1000 50 500 50 500

i try

SELECT NAME, SUM(QUANT_IN) AS QUANT_IN, SUM(VAL_IN) AS VAL_IN,

SUM(QUANT_OUT) AS QUANT_OUT, SUM(VAL_OUT) AS VAL_OUT,

(SELECT SUM(QUANT_IN) - SUM(QUANT_OUT)) AS QUANT_FINAL,

(SELECT SUM(VAL_IN) - SUM(VAL_OUT)) AS VAL_FINAL, FROM TABLE

GROUP BY NAME

and the message is capability not suported. How can i do this.

Please help me.

Thanks!

ScottWhigham
Starting Member

49 Posts

Posted - 2009-09-03 : 08:54:51

In case you didn't notice, this is a Microsoft SQL Server forum. I don't think there are a lot of folks here that can (or want to) help with dBase queries.

========================================================

I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-03 : 09:14:10

SELECT NAME, SUM(QUANT_IN) AS QUANT_IN, SUM(VAL_IN) AS VAL_IN,

SUM(QUANT_OUT) AS QUANT_OUT, SUM(VAL_OUT) AS VAL_OUT,

SUM(QUANT_IN) - SUM(QUANT_OUT) AS QUANT_FINAL,

SUM(VAL_IN) - SUM(VAL_OUT) AS VAL_FINAL FROM TABLE

GROUP BY NAME

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources