SQL Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:

I have a SQL question. If this is the improper board to post this question
on, please let me know where you would suggest I post it.

I have a SALES table with the following schema and structure.

ID Status Date
-- ------ ----
1 Open 1-1-2000
2 Open 1-1-2001
3 Closed 1-1-2002
4 Open 1-1-2003
5 Closed 1-1-2004

I want to write a query that returns all the records where it first does a
count of all the statuses, identifies where the count status is > 2, then

display each record where the status = the status which has been identified
as having a count of > 2.

So the results I want back are:

ID Status Date
-- ------ ----
1 Open 1-1-2000
2 Open 1-1-2001
4 Open 1-1-2003


So I write a query something like this:

SELECT count(status), id, date
FROM sales
GROUP BY status
HAVING count(status) > 2

I get a SQL error saying that I have to add "id" and "date" have to be in
the GROUP BY clause.

But when I put those fields in the GROUP BY, then the count does not
calculate right and I get no results back.

Could anyone enlighten me as to what I could do to fix this?
 
How about this:

SELECT ID, Status, Date
FROM sales
where Status IN (SELECT Status
FROM sales
GROUP BY status
HAVING count(status) > 2
 
Paul

There is not much Adonet or even Dotnet in your question, SQL questions are
more proper in sqlserver newsgroups like

microsoft.public.sqlserver.programming

Cor
 
Back
Top