Query Problem - 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)

Query Problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-26 : 09:28:26

Danyal goerge writes "How can Select fields from another SELECT Statement in SQL SEVER ??

for example ::

How can i do the following SQL Select Statement in SQL SERVER 2000 that using another Select after FROM CLAUSE ??? :

SELECT SUM(CHQ_CNT), SUM(CHQ_AMT) FROM (
SELECT SUM(1) CHQ_CNT,
SUM(CHQ_AMOUNT) CHQ_AMT
FROM ECC_CHEQUES

UNION ALL

SELECT SUM(1) CHQ_CNT,
SUM(CHQ_AMOUNT) CHQ_AMT
FROM HECC_CHEQUES )"

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-26 : 10:10:23

Hi danyal:

The query looks okay to me, all you need to do is add an alias for the subquery:

SELECT SUM(CHQ_CNT), SUM(CHQ_AMT) FROM (
SELECT SUM(1) CHQ_CNT,
SUM(CHQ_AMOUNT) CHQ_AMT
FROM ECC_CHEQUES

UNION ALL

SELECT SUM(1) CHQ_CNT,
SUM(CHQ_AMOUNT) CHQ_AMT
FROM HECC_CHEQUES ) SubQuery1

Of course, that name could be anything of your liking.

P.S. I am curious, never seen this before: using SUM(1) instead of COUNT(*). They return the same result, though I suspect COUNT(*) gives the compiler a chance to use the fastest index.

OS

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-26 : 10:13:48

You're very close, but you must give an alias to the SELECT in parentheses. It doesn't much matter what you call it in this case, since the outer part of the query only uses that derived table (as they're termed in BOL).


SELECT SUM(CHQ_CNT), SUM(CHQ_AMT) FROM (
                  SELECT SUM(1) CHQ_CNT,
                  SUM(CHQ_AMOUNT) CHQ_AMT 
                  FROM ECC_CHEQUES  
           
          UNION ALL 

                  SELECT SUM(1) CHQ_CNT,
                  SUM(CHQ_AMOUNT) CHQ_AMT 
                  FROM HECC_CHEQUES ) AS A

Edit: too slow!

Edited by - Arnold Fribble on 05/26/2003 10:15:23

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-26 : 10:22:09

quote:

P.S. I am curious, never seen this before: using SUM(1) instead of COUNT(*). They return the same result, though I suspect COUNT(*) gives the compiler a chance to use the fastest index.

They will get use the same index, but the behaviour is different: where COUNT(*) returns 0, SUM(1) will return NULL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources