asked on

SQL Percentages

Hi,
I have the table with data as follows:

LocationID GroupID
10 20
10 20
10 20
10 10
10 60
10 20
11 20

I would like to return the locationIDs, etc, where the number of a spcific GroupID,for example 20, for that location is 80% or more of the total for that location.
Any help would be appreciated
Thanks

SOLUTION

Harish Varghese

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

John_Vidmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kjulius

SELECT t1.LocationId, t1.GroupId, LocationGroupIdRcds, LocationIdRcds, CAST(LocationGroupIdRcds * 100 / LocationIdRcds AS DEC(5,2)) AS Pct
FROM (
		SELECT LocationId, GroupId, COUNT(*) AS LocationGroupIdRcds
		FROM #test
		GROUP BY LocationId, GroupId) t1
INNER JOIN (
		SELECT LocationId, COUNT(*) AS LocationIdRcds
		FROM #test
		GROUP BY LocationId) t2
			ON t2.LocationId = t1.LocationId
WHERE CAST(LocationGroupIdRcds * 100 / LocationIdRcds AS DEC(5,2)) >= 80

Open in new window

SOLUTION

kjulius

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial