Sum Not working with Sub Query

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

SQL Server 2000 Forums

SQL Server Development (2000)

Sum Not working with Sub Query

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-07-19 : 14:25:33

Hello, I am trying to sum the of A.cost_ptd as costptd but it shows exactly the same as A.cost_ptd
How can I get the sum of A.cost_ptd?

SELECT A.slspsn_no, A.slspsn_name, A.sls_ptd, A.sls_ytd, A.cost_ptd, A.cost_ytd, SUM(A.cost_ptd) AS costptd
FROM
(SELECT slspsn_no, slspsn_name, sls_ptd, sls_ytd, cost_ptd, cost_ytd
FROM ARSLMFIL2_SQL
WHERE slspsn_no NOT IN ('114', '115', 'WEB')
AND convert(varchar(10), idate, 101) = '07/19/2004'
GROUP BY slspsn_no, slspsn_name, sls_ptd, sls_ytd, cost_ptd, cost_ytd) A,
(SELECT slspsn_no, slspsn_name, sls_ptd, sls_ytd, cost_ptd, cost_ytd
FROM ARSLMFIL2_SQL
WHERE slspsn_no NOT IN ('114', '115', 'WEB')
AND convert(varchar(10), idate, 101) = '07/16/2004'
GROUP BY slspsn_no, slspsn_name, sls_ptd, sls_ytd, cost_ptd, cost_ytd) B
WHERE A.slspsn_no = B.slspsn_no
GROUP BY A.slspsn_no, A.slspsn_name, A.sls_ptd, A.sls_ytd, A.cost_ptd, A.cost_ytd

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-19 : 14:30:09

If you posted the DDL for your tables and some sample data using INSERT INTO statements, then we might be able to help you.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-19 : 14:52:59

just a guess but it looks like you are grouping by and returning too many columns.

do you see that that it makes no sense to GROUP BY column "A" and calculate SUM(A) in the same query?

does this return what you want:

SELECT slspsn_no, slspsn_name, sum(cost_ptd) as Total_Cost_PTD
FROM ARSLMFIL2_SQL
WHERE slspsn_no NOT IN ('114', '115', 'WEB')
AND idate = '2004-07-19'
GROUP BY slspsn_no, slspsn_name

?

and avoid CONVERT() functions around columns you are filtering on. if you need to convert, convert the constant expression.

good luck. read up on SELECT and GROUP BY in books-on-line.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources