Percentage of total in Query

[mafhobb]

Hello everyone.

This query returns three columns, two of them (IssueType and CountofIssueType) are listed on a listbox.

SELECT Calls.SKU, [Issue Types].IssueType, Count(Calls.IssueTypeID) AS CountOfIssueTypeID
FROM Calls INNER JOIN [Issue Types] ON Calls.IssueTypeID = [Issue Types].IssueTypeID
GROUP BY Calls.SKU, [Issue Types].IssueType
HAVING (((Calls.SKU)=[forms]![FrmStatsSpecSKU]![SKU]));

It works well, but I need it to do something else. I need a third column that is the percentage of the total. That is, I need to add all the "CountOfIssueType" to know what the total is and then divide each "CountofIssueType" by this total and have it on the third column.

Is this possible? Can it be done directly in the query?

Thanks

mafhobb

 

[mafhobb]

Wow, nobody wants to take on this one?

Does anyone know if it is possible or not to begin with?

mafhobb

 

[tehNellie]

You'd need a [sub] query that gives you the total of the whole:

SELECT Count(call.issueTypeID) as totalCalls
FROM calls

Save that as qryTotal or something

Use that in your other query, No Join is necessary, it's a one field and applies to every result:

SELECT Calls.SKU, [Issue Types].IssueType, Count(Calls.IssueTypeID) AS CountOfIssueTypeID, 
Count(Calls.IssueTypeID) / qryTotal.totalCalls as PercentOfTotal
FROM Calls , qryTotal
INNER JOIN [Issue Types] ON 
Calls.IssueTypeID = [Issue Types].IssueTypeID
GROUP BY Calls.SKU, [Issue Types].IssueType
HAVING (((Calls.SKU)=[forms]![FrmStatsSpecSKU]![SKU]));

 

[mafhobb]

OK, this first query gives me the total:

SELECT Calls.SKU, Count(Calls.IssueTypeID) AS CountOfIssueTypeID
FROM Calls
GROUP BY Calls.SKU
HAVING (((Calls.SKU)=[forms]![FrmStatsSpecSKU]![SKU]));

This second query gives me the grouped results per IssueTypeID:

SELECT Calls.SKU, [Issue Types].IssueType, Count(Calls.IssueTypeID) AS CountOfIssueTypeID
FROM Calls INNER JOIN [Issue Types] ON Calls.IssueTypeID = [Issue Types].IssueTypeID
GROUP BY Calls.SKU, [Issue Types].IssueType
HAVING (((Calls.SKU)=[forms]![FrmStatsSpecSKU]![SKU]));

How do I combine these?

 

[mafhobb]

This returns a #Error in the Percentoftotal column

SELECT Calls.SKU, [Issue Types].IssueType, Count(Calls.IssueTypeID) AS CountOfIssueTypeID, Count([Calls].[IssueTypeID])/[StatsSKUIssueTypeTotal].[CountOfIssueTypeID] AS PercentOfTotal
FROM Calls INNER JOIN [Issue Types] ON Calls.IssueTypeID = [Issue Types].IssueTypeID
GROUP BY Calls.SKU, [Issue Types].IssueType
HAVING (((Calls.SKU)=[forms]![FrmStatsSpecSKU]![SKU]));

 

[mafhobb]

This Returns a Syntax Error in the Join Operation:

SELECT Calls.SKU, [Issue Types].IssueType, Count(Calls.IssueTypeID) AS CountOfIssueTypeID, Count([Calls].[IssueTypeID])/[StatsSKUIssueTypeTotal].[IssueTypeID] AS PercentOfTotal
FROM Calls, StatsSKUIssueTypeIDTotal 
INNER JOIN [Issue Types] ON Calls.IssueTypeID = [Issue Types].IssueTypeID
GROUP BY Calls.SKU, [Issue Types].IssueType
HAVING (((Calls.SKU)=[forms]![FrmStatsSpecSKU]![SKU]));

 

[mafhobb]

Suggestions anyone?

 

[mafhobb]

Ok, got it done.

I used three different queries:
This one gives me the total

SELECT Calls.SKU, Count(Calls.IssueTypeID) AS CountOfIssueTypeID
FROM Calls
GROUP BY Calls.SKU
HAVING (((Calls.SKU)=[forms]![FrmStatsSpecSKU]![SKU]));

This one gives me the results per IssueType:

SELECT Calls.SKU, [Issue Types].IssueType, Count(Calls.IssueTypeID) AS CountOfIssueTypeID
FROM [Stats SKU Issue Type Total], Calls INNER JOIN [Issue Types] ON Calls.IssueTypeID = [Issue Types].IssueTypeID
GROUP BY Calls.SKU, [Issue Types].IssueType
HAVING (((Calls.SKU)=[forms]![FrmStatsSpecSKU]![SKU]));

And finally this joints it all together and delivers the results

SELECT [Stats SKU Issue Type].IssueType, [Stats SKU Issue Type].CountOfIssueTypeID, [Stats SKU Issue Type Total].CountOfIssueTypeID, Format(([Stats SKU Issue Type].[CountofIssueTypeID]/[Stats SKU Issue Type Total].[CountOfIssueTypeID]*100),"Fixed") AS Expr1
FROM [Stats SKU Issue Type], [Stats SKU Issue Type Total], Calls INNER JOIN [Issue Types] ON Calls.IssueTypeID = [Issue Types].IssueTypeID
GROUP BY [Stats SKU Issue Type].IssueType, [Stats SKU Issue Type].CountOfIssueTypeID, [Stats SKU Issue Type Total].CountOfIssueTypeID;

I would have thought that there was an easier way of doing it but..it works fine this way...

Mafhobb