Solved

SQL Percentages

Posted on 2014-07-30
5
144 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
5 Comments
 
LVL 12

Assisted Solution

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

Open in new window

0
 
LVL 69

Assisted Solution

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

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

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 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

Open in new window

0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now