September 17, 2015 at 12:13 am
Hi,
I am trying to get the maximum for stores where there exists more than one in the same region (e.g. top sales).
I want to leave the ones where there is only one per region out of calculation.
E.g.
Store Sales
A 500
B 200
B 100
C 400
C 800
D 100
D 200
D 700
Looking for result set like:
B 200
C 800
D 700
Any help would be appreciated.
September 17, 2015 at 1:39 am
kbab95 (9/17/2015)
Hi,I am trying to get the maximum for stores where there exists more than one in the same region (e.g. top sales).
I want to leave the ones where there is only one per region out of calculation.
E.g.
Store Sales
A 500
B 200
B 100
C 400
C 800
D 100
D 200
D 700
Looking for result set like:
B 200
C 800
D 700
Any help would be appreciated.
Welcome to the forum 🙂
create table #temp_max
(
Store char(1)
,Sales smallint
)
insert into #temp_max values
('A', 500)
,('B',200)
,('B',100)
,('C',400)
,('C',800)
,('D',100)
,('D',200)
,('D',700)
select
Store
,MAX(sales)
from #temp_max
group by Store
having COUNT(store) > 1
drop table #temp_max
Give that a try.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
September 17, 2015 at 1:47 am
Two quick suggestions
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_DATA TABLE
(
Store CHAR(1) NOT NULL
,Sales NUMERIC(12,2) NOT NULL
);
INSERT INTO @SAMPLE_DATA (Store,Sales)
VALUES ( 'A', 500)
,( 'B', 200)
,( 'B', 100)
,( 'C', 400)
,( 'C', 800)
,( 'D', 100)
,( 'D', 200)
,( 'D', 700)
;
/* WINDOW FUNCTION */
;WITH BASE_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY SD.Store
ORDER BY SD.Sales DESC
) AS SS_RID
,COUNT(*) OVER
(
PARTITION BY SD.Store
) AS SS_CNT
,SD.Store
,SD.Sales
FROM @SAMPLE_DATA SD
)
SELECT
BD.Store
,BD.Sales
FROM BASE_DATA BD
WHERE BD.SS_RID = 1
AND BD.SS_CNT > 1;
/* GROUP BY AND HAVING */
SELECT
SD.Store
,MAX(SD.Sales)
FROM @SAMPLE_DATA SD
GROUP BY SD.Store
HAVING COUNT(SD.Store) > 1;
Results (same for both)
Store Sales
----- -------
B 200.00
C 800.00
D 700.00
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply