Optimized Query - 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 2008 Forums

Transact-SQL (2008)

Optimized Query

Author

Topic

GiorgiZautashvili
Starting Member

4 Posts

Posted - 2012-02-08 : 02:11:11

Hi,
I'm doing something like this:



SELECT 
Id, 
Name,
(
SELECT SUM(SomeColumn) from SomeTable
),
(
SELECT SUM(AnotherColumn) from TheSameTable
)

FROM MyTable

The problem is, I don't like to have two exact same queries (SomeTable) just for summing different columns (SomeColumn and AnotherColumn);

so is there any way to query the table one and display sums of the two different columns at the same time?

Thank you

Kristen
Test

22859 Posts

Posted - 2012-02-08 : 03:32:37

[code]SELECT
Id,
Name,
[Total1],
[Total2]
FROM MyTable
JOIN
(
SELECT SUM(SomeColumn) AS [Total1],
SUM(AnotherColumn) AS [Total2]
from SomeTable
) AS X
ON 1=1
[/code]
might be better ways to "join" the SomeTable though ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-08 : 14:16:14

quote:
Originally posted by GiorgiZautashvili

Hi,
I'm doing something like this:

SELECT
Id,
Name,
(
SELECT SUM(SomeColumn) from SomeTable
),
(
SELECT SUM(AnotherColumn) from TheSameTable
)

FROM MyTable

The problem is, I don't like to have two exact same queries (SomeTable) just for summing different columns (SomeColumn and AnotherColumn);

so is there any way to query the table one and display sums of the two different columns at the same time?

Thank you

dont you have any relationship between table used in subquery and main table? then didnt understand purpose of showing count along with the tables resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nerdygirl61
Starting Member

21 Posts

Posted - 2012-02-08 : 20:06:32

With the assumption that SomeTable and MyTable link using the ID field, I think this may be what you are looking for.

SELECT a.ID
,a.Name
,SUM(b.SomeColumn) as SomeColumn
,SUM(b.AnotherColumn) as AnotherColumn
FROM MyTable as a
JOIN SomeTable as b
ON a.ID = b.ID
GROUP BY a.ID
,a.Name

GiorgiZautashvili
Starting Member

4 Posts

Posted - 2012-02-09 : 01:35:20

[/quote]
dont you have any relationship between table used in subquery and main table? then didnt understand purpose of showing count along with the tables resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

[/quote]

Yes, they relate, I skipped this in the snippet for more clarity. In fact I use CTE and relation is between those tables.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources