SQL Question

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?

Re: SQL Question

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"

Re: SQL Question

"Paul" wrote in message
news:D2320D7D-00A3-4D03-9EDC-A3B1E5F9E85D@microsoft.com...
> 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?

To learn more about SQL look at Lesson 5 and 6 videos at:
URL:http://msdn.microsoft.com/vstudio/express/sql/learning/d efault.aspx

Minute 33:00 of Lesson 5 discusses GROUP BY and HAVING;
Minute 1:00 of Lesson 6 discusses Sub-SELECTs.

Perhaps you want:

SELECT id, status, date
FROM sales
WHERE status IN
(SELECT status
FROM sales
GROUP BY status
HAVING COUNT(*) > 2)