November 9, 2010 at 8:11 pm
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
November 9, 2010 at 10:54 pm
First, how about posting the DDL for the tables, sample data for the tables (in a readily consumable format), and the expected results based on the sample data.
For help with this please read the first article I reference below in my signature block regarding asking for help.
Also, if you are getting errors, it would also help to post the exact error messages you are getting to help us diagnose your problem.
November 11, 2010 at 5:04 am
Here is the short answer: if you use any aggregate function in the select list (sum, min, max, count, etc.)
then you MUST either:
A) have ONLY aggregate functions in the select list
or
B) have every non-aggregated item in the Group By clause
Example A:
select count(*), count(distinct name), min(object_id) from sys.objects
Example B:
select type, count(*), count(distinct name), min(object_id)
from sys.objects
group by type
Also your "sample" code is missing at least one comma and the case statement is missing the end keyword. That said, here is how it could work:
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 end
from tbl1 t
INNER JOIN tbl2 t1 on t.ID = T1.ID
where t1.value7 = ' '
t.value8 = ' '
group by t.value,
t.value1,
t.value3,
convert(varchar(30),t1.date,110),
t1.value5,
case when t.value = 'y' then t1.value6 end
And once you do the group by the distinct is Superfluous.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply