Count & Percentage (1 Viewer)

bigal.nz · Dec 7, 2017

Hi All,

I have a query which gives me a count of each value for Pathway

Code:

SELECT NTH.Pathway, Count(NTH.Pathway) AS [Count]
FROM NTH
WHERE DateStart Between #01/07/2017# AND #10/11/2017#
GROUP BY NTH.Pathway;

This works nicely. I am wonderng if there is a way to add a colum which calculates the count as a percentage of the total?

Thanks

-Al

plog · Dec 7, 2017

First, I wouldn't use 'Count' as an alias name--it's a reserved word and will get confusing and possibly cause issues with the query. Name it PathwayCount.

From there you would use a subquery to get the total:

Code:

SELECT Pathway, COUNT(Pathway) AS PathWayCount
  , COUNT(PathWay)/(SELECT COUNT(PathWay) FROM NTH) AS PathwayPercent
FROM NTH
GROUP BY Pathway

Not tested, but the theory is good. Any WHERE clauses would have to be added to both SELECTs.

bigal.nz · Dec 7, 2017

plog said:
First, I wouldn't use 'Count' as an alias name--it's a reserved word and will get confusing and possibly cause issues with the query. Name it PathwayCount.

From there you would use a subquery to get the total:

Code:

SELECT Pathway, COUNT(Pathway) AS PathWayCount , COUNT(PathWay)/(SELECT COUNT(PathWay) FROM NTH) AS PathwayPercent FROM NTH GROUP BY Pathway

Not tested, but the theory is good. Any WHERE clauses would have to be added to both SELECTs.

Just gave it a go - seems to work, but the results are often expressed as :

6.02743646498E-02 etc

Is there a way to control the formatting here to a few decimal places? perhaps

Code:

INT (  COUNT(PathWay)/(SELECT COUNT(PathWay) FROM NTH) AS PathwayPercent )

or

Code:

ROUND ( COUNT(PathWay)/(SELECT COUNT(PathWay) FROM NTH) AS PathwayPercent, 2 )

Cheers

-Al

bigal.nz · Dec 7, 2017

Dont worry - it works nicely with round when I put the brackets in the right place.

Count & Percentage (1 Viewer)

bigal.nz

Registered User.

plog

Banishment Pending

bigal.nz

Registered User.

bigal.nz

Registered User.

Similar threads

Users who are viewing this thread