Multiple count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple count

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-03-13 : 09:54:01

I have a a table tblOrders

When I have a question about an order I send the client a fax with a question.
Sometimes later on I send another fax with a question.

Now I want to make a query which gives back as a result the amount of times I send more than one fax about an order e.g.:

Orders where I send one fax : 1200
Orders where I send two faxes : 250
Orders where I send three faxes: 57
Orders where I send four faxes : 0

I have a table tblFax where each row contains the ID of the Order.

The things is: I made an ASP.NET report generator AND I can only parse SELECT statements to this report. I cannot use Stored Procedures nor temp tables....

TIA,

Henri

~~~
SQL is nothing, writing it everything.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-03-13 : 09:57:04


SELECT faxCount, COUNT(*)
FROM (
    SELECT COUNT(*) AS faxCount
    FROM tblFax 
    GROUP BY orderID
  ) AS A
GROUP BY faxCount

Edited by - Arnold Fribble on 03/13/2003 09:57:56

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-13 : 10:02:09

SELECT Total , Count(*) HowMany
FROM
(
SELECT OrderID, Count(*) Total
FROM tblFax
GROUP BY OrderID
) S

GROUP BY Total
ORDER BY Total ASC

Needs debugging, but it should work without a stored procedure. This query will not return a row if HowMany is zero. This may be important because the last row in your example shows four faxes: 0

Sam

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-03-13 : 10:11:41

I didn't expect an answer so soon, but YES this is it!

Wow, this is *really* impressive

Henri

~~~
SQL is nothing, writing it everything.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources