SELECT AND SUM - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT AND SUM

Author

Topic

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2004-11-18 : 02:23:06

HI

SELECT

(SELECT DATEPART(WW,OPBEGDATE)) AS 'HAFTA',
(SELECT DATEPART(WW,OPDUEDATE)) AS 'THAFTA',
T.ITEMREF AS 'ITEMREF',
T.PRODORDREF AS 'URETIMREF',
(SELECT T.LOGICALREF ) AS 'EMIRREF',
RIGHT(T.LINENO_,1) AS 'SIRA',
(SELECT CASE WHEN T.PRINTCNT >0 THEN 'R' WHEN T.PRINTCNT = -1 THEN 'P' ELSE '' END ) AS 'RP.',

(SELECT CASE WHEN T.LINESTATUS=0 THEN '' WHEN T.LINESTATUS=1 THEN 'P' END) AS 'DUR.',

(SELECT SUM(AMOUNT) FROM TABLE1 WHERE SOURCEPOLNREF=T.LOGICALREF) AS 'G. MIKTARI',

FROM TABLE T WHERE LINESTATUS IN (0,1)

I USE "SUM" IN THIS QUERY BUT IT IS ERRORR....
I DO not know why is error..

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-18 : 02:50:22

what's the error message? does the amount field contain null values?

[/quote]

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2004-11-18 : 03:09:20

What's with all the other SELECTs?


SELECT 

(SELECT DATEPART(WW,OPBEGDATE)) AS 'HAFTA',
(SELECT DATEPART(WW,OPDUEDATE)) AS 'THAFTA',
T.ITEMREF AS 'ITEMREF',
T.PRODORDREF AS 'URETIMREF',
(SELECT T.LOGICALREF ) AS 'EMIRREF',
RIGHT(T.LINENO_,1) AS 'SIRA',
(SELECT CASE WHEN T.PRINTCNT >0 THEN 'R' WHEN T.PRINTCNT = -1 THEN 'P' ELSE '' END ) AS 'RP.',

(SELECT CASE WHEN T.LINESTATUS=0 THEN '' WHEN T.LINESTATUS=1 THEN 'P' END) AS 'DUR.',

(SELECT SUM(AMOUNT) FROM TABLE1 WHERE SOURCEPOLNREF=T.LOGICALREF) AS 'G. MIKTARI', 

FROM TABLE T 
WHERE LINESTATUS IN (0,1)

Kristen

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2004-11-18 : 05:08:31

ERROR IS

subquery returned more than 1 values.this is not permitted when the subquery follows=,,<,> or when the subqeury is used as an expression

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-18 : 06:38:55

try kristen's solution...

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources