Need a help on a SQL Query

Could somebody please help me on following SQL Query.
Single table data likes this:
SerialNumber PassedTestType
20001 1
20001 2
20001 3
20001 4
20001 5
20002 1
20002 2
20002 3
20003 1
20003 2
20004 1
.............
.............
There are total 5 tests (1-5 indicate each test). A new record will be
added in when it passed a test. Unit always start from test 1 to test 5
I want to create a simple report like this:
TestType Total number of units in this test stage
1 2356
2 456
3 4352
4 176
5 36253
How to write a SQL query like above Work In Process Report ?
Thanks.Try:
select
TestType
, count (*)
from
MyTable
group by
TestType
order by
TestType
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<wubin_98@.yahoo.com> wrote in message
news:1140653005.105032.132270@.g44g2000cwa.googlegroups.com...
Could somebody please help me on following SQL Query.
Single table data likes this:
SerialNumber PassedTestType
20001 1
20001 2
20001 3
20001 4
20001 5
20002 1
20002 2
20002 3
20003 1
20003 2
20004 1
.............
.............
There are total 5 tests (1-5 indicate each test). A new record will be
added in when it passed a test. Unit always start from test 1 to test 5
I want to create a simple report like this:
TestType Total number of units in this test stage
1 2356
2 456
3 4352
4 176
5 36253
How to write a SQL query like above Work In Process Report ?
Thanks.|||If I understand correctly, you only want the last test passed for each
SerialNumber to be counted.
SELECT LastPassed, count(SerialNumber)
FROM (select SerialNumber,
max(PassedTestType) as LastPassed
from TestHistory
group by SerialNumber) as T
GROUP BY LastPassed
ORDER BY LastPassed
Roy
On 22 Feb 2006 16:03:25 -0800, "wubin_98@.yahoo.com"
<wubin_98@.yahoo.com> wrote:

>Could somebody please help me on following SQL Query.
>Single table data likes this:
>SerialNumber PassedTestType
>20001 1
>20001 2
>20001 3
>20001 4
>20001 5
>20002 1
>20002 2
>20002 3
>20003 1
>20003 2
>20004 1
>.............
>.............
>There are total 5 tests (1-5 indicate each test). A new record will be
>added in when it passed a test. Unit always start from test 1 to test 5
>I want to create a simple report like this:
>TestType Total number of units in this test stage
>1 2356
>2 456
>3 4352
>4 176
>5 36253
>How to write a SQL query like above Work In Process Report ?
>Thanks.|||Roy,
It is just what I need.
Thank you very much!