?
Solved

SQL Percentages

Posted on 2014-07-30
5
Medium Priority
?
160 Views
Last Modified: 2014-08-05
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
Comment
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
5 Comments
 
LVL 12

Assisted Solution

by:Harish Varghese
Harish Varghese earned 500 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

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 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

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

Open in new window

0
 
LVL 1

Expert Comment

by:kjulius
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

Open in new window

0
 
LVL 1

Assisted Solution

by:kjulius
kjulius earned 500 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

Open in new window

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

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

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

Join & Ask a Question