Grouping question
-
I have data similar to the following that I am trying to group:
EmpID, LocationID, Amount
1, 1, 15.00
1, 1, 20.00
1, 1, 25.00
1, 2, 30.00
2, 3, 10.00
2, 4, 15.00
2, 4, 15.00
2, 4, 20.00
Currently I am grouping by EmpID, taking the Max of LocationID, and summing the amount. eg:
SELECT EmpID, MAX(LocationID), SUM(Amount) AS SumOfAmount
FROM tblTransactions
GROUP BY EmpID
Which returns the following:
EmpID, LocationID, Amount
1, 2, 90.00
2, 4, 60.00
However, rather than taking the max of the LocationID, it would be better to choose the LocationID for each EmpID that had the most records associated with it. Therefore, after grouping the data above should look like this:
EmpID LocationID Amount
1, 1, 90.00
2, 4, 60.00
Any suggestions on how this might be done?
-
Hey Kevin,
Can you please tell me what you exactly trying to achieve?
I mean to say can you brief this topic more,
its seems the topic is not clear to me.
Cheers!
Sandy.
--
-
WITH Counts (EmpID, LocationID, Number) AS
(SELECT EmpID, LocationID, COUNT(*) FROM tblTransactions GROUP BY EmpID, LocationID)
SELECT A.EmpID,
(SELECT MAX(Z.LocationID) FROM Counts Z WHERE Z.EmpID = A.EmpID AND Z.Number = (SELECT MAX(Y.Number) FROM Counts Y WHERE Y.EmpID = A.EmpID)),
SUM(A.Amount)
FROM tblTransactions A
GROUP BY A.EmpID
-
We want to take a daily summary pf payments collected for each employee. Some employees take payments at multiple locations. When we do our summary (grouping) we need to have a location associated with that employee. At present we are just using the max of the location ID. The best option is to have that location be the one at which the most payments were taken.
-
with CTE1 as
(select EmpID, LocationID,
count(*) over(partition by EmpID,LocationID) as cn,
sum(Amount) over(partition by EmpID) as Amount
from tblTransactions),
CTE2 as
(select EmpID, LocationID, Amount,
row_number() over(partition by EmpID order by cn desc) as rn
from CTE1)
select EmpID, LocationID, Amount
from CTE2
where rn=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
MarkC (10/30/2007)
with CTE1 as(select EmpID, LocationID,
count(*) over(partition by EmpID,LocationID) as cn,
sum(Amount) over(partition by EmpID) as Amount
from tblTransactions),
CTE2 as
(select EmpID, LocationID, Amount,
row_number() over(partition by EmpID order by cn desc) as rn
from CTE1)
select EmpID, LocationID, Amount
from CTE2
where rn=1
Is this only an option for SQL Server 2005? I am using SQL 2000. I got a syntax error when I tried this and I don't find it in the BOL for SQL 2000 while I do find "WITH CTE" in the BOL for SQL 2005.
-
Afraid so, 'WITH' is SQL Server 2005 only and is used for CTEs (common table expressions).
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Using Koji's example with CTEs and converting it to be 2000 compatible.
This works, but is messy.
CREATE TABLE #tmp (EmpID INT , LocationID INT , Amount NUMERIC(9,2))
INSERT #tmp
SELECT 1, 1, 15.00 UNION ALL
SELECT 1, 1, 20.00 UNION ALL
SELECT 1, 1, 25.00 UNION ALL
SELECT 1, 2, 30.00 UNION ALL
SELECT 2, 3, 10.00 UNION ALL
SELECT 2, 4, 15.00 UNION ALL
SELECT 2, 4, 15.00 UNION ALL
SELECT 2, 4, 20.00
SELECT
a.EmpID
,(SELECT MAX(b.LocationID)
FROM
(SELECT EmpID, LocationID, COUNT(*) AS number
FROM #tmp
GROUP BY EmpID, LocationID) b
WHERE b.EmpID = a.EmpID
AND b.Number = (SELECT MAX(c.Number)
FROM
(SELECT EmpID, LocationID, COUNT(*) AS number
FROM #tmp
GROUP BY EmpID, LocationID) c
WHERE c.EmpID = a.EmpID))
,SUM(a.Amount) AS SumOfAmount
FROM #tmp a
GROUP BY a.EmpID
DROP TABLE #tmp
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Thanks, Jason. That was just what I needed.
-
This a little "simpler 😉
CREATE TABLE #tmp (EmpID INT , LocationID INT , Amount NUMERIC(9,2))
INSERT #tmp
SELECT 1, 1, 15.00 UNION ALL
SELECT 1, 1, 20.00 UNION ALL
SELECT 1, 1, 25.00 UNION ALL
SELECT 1, 2, 30.00 UNION ALL
SELECT 2, 3, 10.00 UNION ALL
SELECT 2, 4, 15.00 UNION ALL
SELECT 2, 4, 15.00 UNION ALL
SELECT 2, 4, 20.00
SELECT
a.EmpID
, a.LocationID
, SUM(a.Amount) AS SumOfAmount
FROM #tmp a
GROUP BY a.EmpID, a.LocationID
HAVING SUM(a.Amount) = (SELECT Top 1 SUM(b.Amount)Amt
FROM #tmp b
WHERE a.EmpID = b.EmpID
GROUP BY b.locationID
order by 1 desc )
DROP TABLE #tmp
* Noel -
NICE noeld
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
noeld (10/30/2007)
This a little "simpler 😉CREATE TABLE #tmp (EmpID INT , LocationID INT , Amount NUMERIC(9,2))
INSERT #tmp
SELECT 1, 1, 15.00 UNION ALL
SELECT 1, 1, 20.00 UNION ALL
SELECT 1, 1, 25.00 UNION ALL
SELECT 1, 2, 30.00 UNION ALL
SELECT 2, 3, 10.00 UNION ALL
SELECT 2, 4, 15.00 UNION ALL
SELECT 2, 4, 15.00 UNION ALL
SELECT 2, 4, 20.00
SELECT
a.EmpID
, a.LocationID
, SUM(a.Amount) AS SumOfAmount
FROM #tmp a
GROUP BY a.EmpID, a.LocationID
HAVING SUM(a.Amount) = (SELECT Top 1 SUM(b.Amount)Amt
FROM #tmp b
WHERE a.EmpID = b.EmpID
GROUP BY b.locationID
order by 1 desc )
DROP TABLE #tmp
DECLARE @X TABLE (EmpID int, LocationID int, Number int)
INSERT INTO @X SELECT EmpID, LocationID, COUNT(*) FROM @tblTransactions GROUP BY EmpID, LocationID
SELECT A.EmpID,
(SELECT MAX(Z.LocationID) FROM @X Z WHERE Z.EmpID = A.EmpID AND Z.Number = (SELECT MAX(Y.Number) FROM @X Y WHERE Y.EmpID = A.EmpID)),
SUM(A.Amount)
FROM @tblTransactions A
GROUP BY A.EmpID
Need to take care of ties.
So I used MAX(Z.LocationID)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply