Solved

SQL Percentages

Posted on 2014-07-30
5
155 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 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

627 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