Solved

SQL Percentages

Posted on 2014-07-30
149 Views
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
0
Question by:Morpheus7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions

LVL 12

Assisted Solution

Harish Varghese earned 125 total points
ID: 40229894
``````Create Table #test (LocationId int, GroupId int)
Insert into #test values
(10, 20),
(10, 20),
(10, 20),
(10, 10),
(10, 60),
(10, 20),
(11, 20)

Select A.* from #test A,
(Select LocationId, SUM(GroupId) GroupSum
From #test Group by LocationId) B
Where A.LocationId = B.LocationId
And A.GroupId >= B.GroupSum * 0.8
``````
0

LVL 69

Assisted Solution

Scott Pletcher earned 125 total points
ID: 40229981
IF OBJECT_ID('tempdb.dbo.#GroupCounts') IS NOT NULL
DROP TABLE #GroupCounts

SELECT
LocationId, GroupId, COUNT(*) AS GroupCount
INTO #GroupCounts
FROM #test
GROUP BY
LocationId, GroupId WITH ROLLUP

SELECT gc.LocationId, gc.GroupId, gc.GroupCount
FROM #GroupCounts gc
WHERE
gc.GroupId IS NOT NULL AND
gc.GroupCount >= (SELECT gc2.GroupCount FROM #GroupCounts gc2 WHERE gc2.GroupId IS NULL AND gc2.LocationId = gc.LocationId) * 0.8
0

LVL 11

Accepted Solution

John_Vidmar earned 125 total points
ID: 40230447
``````SELECT	LocationId
FROM	mytable
GROUP
BY	LocationId
HAVING	( SUM(CASE WHEN GroupId = 20 THEN 1.0 END) / COUNT(*) ) >= 0.8
``````
0

LVL 1

Expert Comment

ID: 40232632
``````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
``````
0

LVL 1

Assisted Solution

kjulius earned 125 total points
ID: 40232686
or the same using windowing:

``````SELECT *, CAST(LocationGroupIdRcds * 100 / LocationIdRcds AS DEC(5,2)) AS Pct
FROM (
SELECT LocationId, GroupId, COUNT(*) OVER(PARTITION BY LocationId, GroupId) AS LocationGroupIdRcds, COUNT(*) OVER(PARTITION BY LocationId) AS LocationIdRcds
FROM #Test) t1
WHERE CAST(LocationGroupIdRcds * 100 / LocationIdRcds AS DEC(5,2)) >= 80
``````
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses
Course of the Month4 days, 16 hours left to enroll

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.