aggregate ... 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

General SQL Server Forums

New to SQL Server Programming

aggregate ... problem

Author

Topic

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2010-11-09 : 19:38:09

Hi,

I have a query like this

select distinct t.value,t.value1,t.value3,sum(t.value4)
convert(varchar(30),t1.date,110),t1.value5,case when t.value = 'y' then t1.value6
from tbl1 t INNER JOIN tbl2 t1 on t.ID = T1.ID
where t1.value7 = ' '
t.value8 = ' '

i am getting error if i run the above query b'coz of sum , even if i used group by i am getting different errors, how can i do that in the other way

in the above query i mentiond .value1, .value 2 ... those are not exactly values in that field, those are some varchar()'s , datetime, id number ...

can any one help me out in this

dev

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-10 : 02:58:32

Try this -


select distinct t.value,t.value1,t.value3, tt2.value4
convert(varchar(30),t1.date,110),t1.value5,case when t.value = 'y' then t1.value6
from tbl1 t INNER JOIN tbl2 t1 on t.ID = T1.ID
CROSS APPLY
(
SELECT sum(value4) as value4 from tbl1 tt INNER JOIN tbl2 tt1 on tt.ID = tt1.ID
where tt1.value7 = ' '
tt.value8 = ' '
) tt2
where tt1.value7 = ' '
tt.value8 = ' '

Vaibhav T

If I cant go back, I want to go fast...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-10 : 10:13:31

quote:

Originally posted by vaibhavktiwari83

Try this -


select distinct t.value,t.value1,t.value3, tt2.value4
convert(varchar(30),t1.date,110),t1.value5,case when t.value = 'y' then t1.value6
from tbl1 t INNER JOIN tbl2 t1 on t.ID = T1.ID
CROSS APPLY
(
SELECT sum(value4) as value4 from tbl1 tt INNER JOIN tbl2 tt1 on tt.ID = tt1.ID
where tt1.value7 = ' '
tt.value8 = ' '
) tt2
where tt1.value7 = ' ' and
tt.value8 = ' '

Vaibhav T

If I cant go back, I want to go fast...

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-11 : 01:54:57

quote:
Originally posted by visakh16

quote:
Originally posted by vaibhavktiwari83

Try this -

select distinct t.value,t.value1,t.value3, tt2.value4
convert(varchar(30),t1.date,110),t1.value5,case when t.value = 'y' then t1.value6
from tbl1 t INNER JOIN tbl2 t1 on t.ID = T1.ID
CROSS APPLY
(
SELECT sum(value4) as value4 from tbl1 tt INNER JOIN tbl2 tt1 on tt.ID = tt1.ID
where tt1.value7 = ' ' and
tt.value8 = ' '
) tt2
where tt1.value7 = ' ' and
tt.value8 = ' '

Vaibhav T

If I cant go back, I want to go fast...

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

Thanks

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources