sql query for dropdownlist

[Dilip H Patel]

Dear .netman, first of all you can not use aggregate functions (like sum, avg) without using Group by clause. and another thing also you can not use aggregate functions in where clause. you have to use Having clause with group by clause. select * from table where field1 not in (select field2 from table2 where field3 like '%05/09/2008%' gruop by field2 having sum (field3) < 1) Regards, Dilip Patel

[Blue_Boy]

try this select * from table where field1 not in (select field2 from table2 where field3 like '%05/09/2008%' and (select top 1 sum(t1.field3) from table2 as t1 where t1.field3= table2.field3 group by t1.field3) < 1

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.

[eyeseetee]

Hi OK Im not sure how to write this query but Im going to explain because the code above works but it doesnt do what I want. Basically I need to do check how many rows in another table contain the number 2 and match a certain date. so something like select * from table where field1 not in (select field2 from table2 where field3 like '%05/09/2008%' and sum number of fields containing '2' < 1) any help on this would be great thanks

The answers posted by me are suggestions only and cannot be used in anyway against me.

[Blue_Boy]

Which result you get from my query and which result you want to get?

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.

[eyeseetee]

Well to be honest i used the others guys query because I didnt understand what you mean by t1 :sigh:

The answers posted by me are suggestions only and cannot be used in anyway against me.

[Blue_Boy]

Anyway :) ,if you want post here result which u get from query and post result which you want.

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.

[eyeseetee]

It doesnt work, it just shows errors in the sql like incorrect syntax near '1' thanks anyway!

The answers posted by me are suggestions only and cannot be used in anyway against me.

[Blue_Boy]

.netman wrote:

thanks anyway!

No problem,it would be my pleasure if I could help you.

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.

[AprNgp]

@ Blue_Boy i think in the query u provided, something like, SELECT * FROM table AS t1 is missing ....

Apurv

[Blue_Boy]

I have already specify table2 as t1 on my first post for this question.

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.