Solved

SQL Percentages

Posted on 2014-07-30
5
145 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:Scott Pletcher
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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 34
Query Syntax 17 32
ms sql + get number in list out of total 7 26
New to SSRS, extremely slow running report. 8 19
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

813 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

17 Experts available now in Live!

Get 1:1 Help Now