[quoted text, click to view] Paul wrote:
> 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?
You need to use a subquery. The following will work in SQL Server (you
neglected to tell us what database type and version your are using, so don't
blame me if this is not applicable to you):
First you need to get the count of each status setting:
Select status, count(*) As statuscount
From sales
Group By status
If this is something you will be using in other queries, or if you wish to
simplify the next query, you can create a view:
CREATE VIEW StatusCounts AS
Select status, count(*) As statuscount
From sales
Group By status
Now that you have the status counts, you can use the above view to filter
the rows from the Sales table by using an inner join.
Either:
Select ID, s.Status, [Date]
From sales s inner join (
Select status, count(*) As statuscount
From sales
Group By status) q
ON s.status=q.status
WHERE statuscount>2
Or, if you chose to create the view:
Select ID, s.Status, [Date]
From sales s inner join StatusCounts q
ON s.status=q.status
WHERE statuscount>2
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
