Re: Conditional Count with multiple expressions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

hi,

JDubs wrote:
I have a table that looks somewhat like this:

Group JobType JobStatus
A Parts Complete
A Repair New
A Parts Complete
B Repair Complete B Parts New

I want to query a count of a certain JobStatus for each JobType i.e. JobStatus="Complete", also a count all of the Jobs and then calculate a percentage for jobs complete CountOfCompleteJobStatus/CountOfTotalJobs AS JobStatusPercent. I'd like to group by JobType.
This should work:

SELECT Q.[JobType],
Nz(R.[Completed], 0) / Q.[Total] AS [Percentage]
FROM
(
SELECT [JobType],
Count(*) AS [Total]
FROM [yourTable]
GROUP BY [JobType]
) Q
LEFT JOIN
(
SELECT [JobType],
Count(*) AS [Completed]
FROM [yourTable]
WHERE [JobStatus] = "Completed"
GROUP BY [JobType]
) R ON R.[JobType] = Q.[JobType]



mfG
--> stefan <--
.

Relevant Pages

  • Conditional Count with multiple expressions
    ... I want to query a count of a certain JobStatus for each JobType i.e. ... Aggregate for the all Groups together. ...
    (microsoft.public.access.queries)
  • Re: Conditional Count with multiple expressions
    ... How can include percentages for each of the individual group as well as the ... Group JobType JobStatus ... I want to query a count of a certain JobStatus for each JobType i.e. ... percentage for jobs complete CountOfCompleteJobStatus/CountOfTotalJobs AS ...
    (microsoft.public.access.queries)
  • Re: Conditional Count with multiple expressions
    ... How can include percentages for each of the individual group as well as the ... Group JobType JobStatus ... I want to query a count of a certain JobStatus for each JobType i.e. ... percentage for jobs complete CountOfCompleteJobStatus/CountOfTotalJobs AS ...
    (microsoft.public.access.queries)
  • Re: SELECT QUESTION
    ... table2 for the job type that matches in both tables. ... I am using Sql Server 2000. ... CREATE TABLE jobs (jobtype varchar(20) NOT NULL PRIMARY KEY, ...
    (comp.databases.ms-sqlserver)
  • php form info...
    ... OR jobtype like '%$var%' ... query works great, except when the string contains the 4-letter word ... then the mysql query breaks down and give the usual: ... You have an error in your SQL syntax. ...
    (comp.lang.php)