Sub Query from Single Table - 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 2005 Forums

Transact-SQL (2005)

Sub Query from Single Table

Author

Topic

rickincanada
Starting Member

18 Posts

Posted - 2011-08-26 : 13:45:57

I am attempting to get some data from a single table using a sub select to sum a particular column however I'm stuck trying to get the sub select down to the appropriate values...here's my issue:

SELECT column1
, column2
, column3
, (SELECT SUM (column4 - column5) FROM table WHERE column1 = column1) AS 'SUM'
FROM table

In the above example column1 is a date and I'm only wanting to SUM column 4 - column5 for each day. The trouble I have is I don't have a single other table that can be joined to this one in the sub query to match the dates. Is this possible or am I chasing down the impossible?

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-26 : 14:26:55

try this:


SELECT t.column1
, t.column2
, t.column3
, d.daySum
FROM table t
join (
       select column1
              ,sum(column4 - column5) as [daySum]
       from   table
       group by column1
       ) d
              on d.column1 = t.column1

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-27 : 06:27:46

[code]
SELECT t.column1
, t.column2
, t.column3
,sum(t.column4 - t.column5) over (partition by t.column1) AS daySum
FROM table t
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources