Conditional Count with multiple expressions

  • Thread starter Thread starter JDubs
  • Start date Start date
J

JDubs

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.

Thats just the beginning, then I need to do this for each Group and an
Aggregate for the all Groups together. I assume for this part it might be
easier to do each individual group and the aggregate seperately and then just
merge them in a report, but it would be heavenly to have them all in the same
query (as nasty as it might be).

I keep getting "You tried to execute a query that does not include the
specified expression 'CountOfJobStatus' as part of an aggregate function.

Any help you can provide would be greatly appreciated.
 
hi,
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.
Click to expand...
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 <--
 
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 said:
hi,
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.
Click to expand...
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 <--
Click to expand...
 
Thanks for your help. That works great, 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 in advance
though.


Stefan Hoffmann said:
hi,
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.
Click to expand...
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 <--
Click to expand...
 
hi,
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 in advance
though.
Click to expand...
Include the group in both sub-queries and add it to the field list of
the outer select. Save it as query. Create a pivot query using it as source.


mfG
--> stefan <--
 
Back
Top