Re: Conditional Count with multiple expressions

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

Thanks for your help. I got that to work, but I want to take it one step
further.

How can include percentages for each of the individual group as well as the
whole?

My results table will be big, but it would ideally look like this:

JobType Group A % Group B % Total %
Parts 100% 0% 66%
Repair 0% 100% 50%

Is that even possible? I know I"m pushing my luck. Thanks.



"Stefan Hoffmann" wrote:

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
    ... Group JobType JobStatus ... 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. ...
    (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)