Prev: UPDATE in UDF
Next: Merge membership
From: David Olsen on 2 Jun 2010 13:12
I have the following table with no primary key.



CREATE TABLE Sales

(

SalesPersonID INT NOT NULL,

FirstName CHAR(15) NULL,

LastName CHAR(15) NULL,

Gender CHAR(1) NULL

CHECK (Gender IN ('M','F')),

Quarter INT NULL,

SalesYear INT NULL,

SalesAmount DECIMAL(21,13) NULL,

QuarterlyBonus DECIMAL(21,13) NULL,

Region CHAR(15) NULL

CHECK (Region IN
('Northwest','Southwest','Southeast','Northeast','Midwest')),

)







This query gives me the person that sold the most overall but I need the
person that sold the most for each region. I know I could include a WHERE
clause and UNION five queries together but I was wondering how to do it more
elegantly.



SELECT SalesPersonID, Region, SUM(SalesAmount) AS HighestAmount
FROM Sales
GROUP BY SalesPersonID, Region
HAVING SUM(SalesAmount) >= ALL
(SELECT SUM(SalesAmount)
FROM Sales
GROUP BY SalesPersonID);







Thank you,



David Olsen

From: Tom Cooper on 2 Jun 2010 14:55
One way

With SalesByPersonRegion As
(SELECT SalesPersonID, Region, SUM(SalesAmount) AS SumSales
FROM Sales
GROUP BY SalesPersonID, Region),
SalesOrderedByRegion As
(SELECT SalesPersonID, Region, SumSales,
ROW_NUMBER() OVER(PARTITION BY Region ORDER BY SumSales DESC) AS rn
FROM SalesByPersonRegion)
SELECT SalesPersonID, Region, SumSales AS HighestAmount
FROM SalesOrderedByRegion
WHERE rn=1;

Tom

"David Olsen" <david.olsen(a)usu.edu> wrote in message
news:87CBA942-6174-42A6-82FA-9BCBF92BCE99(a)microsoft.com...
>I have the following table with no primary key.
>
>
>
> CREATE TABLE Sales
>
> (
>
> SalesPersonID INT NOT NULL,
>
> FirstName CHAR(15) NULL,
>
> LastName CHAR(15) NULL,
>
> Gender CHAR(1) NULL
>
> CHECK (Gender IN ('M','F')),
>
> Quarter INT NULL,
>
> SalesYear INT NULL,
>
> SalesAmount DECIMAL(21,13) NULL,
>
> QuarterlyBonus DECIMAL(21,13) NULL,
>
> Region CHAR(15) NULL
>
> CHECK (Region IN
> ('Northwest','Southwest','Southeast','Northeast','Midwest')),
>
> )
>
>
>
>
>
>
>
> This query gives me the person that sold the most overall but I need the
> person that sold the most for each region. I know I could include a WHERE
> clause and UNION five queries together but I was wondering how to do it
> more elegantly.
>
>
>
> SELECT SalesPersonID, Region, SUM(SalesAmount) AS HighestAmount
> FROM Sales
> GROUP BY SalesPersonID, Region
> HAVING SUM(SalesAmount) >= ALL
> (SELECT SUM(SalesAmount)
> FROM Sales
> GROUP BY SalesPersonID);
>
>
>
>
>
>
>
> Thank you,
>
>
>
> David Olsen
>

From: obiron via SQLMonster.com on 3 Jun 2010 09:05
If you are using 2005/2008 then create the grouping that sums the activity as
a CTE then use the RANK() function to rate each person in each area.
Partition by year/qtr/area and order by value

You can then select every record from the CTE where they are #1 ranked

Obiron

David Olsen wrote:
>I have the following table with no primary key.
>
>CREATE TABLE Sales
>
>(
>
> SalesPersonID INT NOT NULL,
>
> FirstName CHAR(15) NULL,
>
> LastName CHAR(15) NULL,
>
> Gender CHAR(1) NULL
>
> CHECK (Gender IN ('M','F')),
>
> Quarter INT NULL,
>
> SalesYear INT NULL,
>
> SalesAmount DECIMAL(21,13) NULL,
>
> QuarterlyBonus DECIMAL(21,13) NULL,
>
> Region CHAR(15) NULL
>
> CHECK (Region IN
>('Northwest','Southwest','Southeast','Northeast','Midwest')),
>
>)
>
>This query gives me the person that sold the most overall but I need the
>person that sold the most for each region. I know I could include a WHERE
>clause and UNION five queries together but I was wondering how to do it more
>elegantly.
>
>SELECT SalesPersonID, Region, SUM(SalesAmount) AS HighestAmount
> FROM Sales
> GROUP BY SalesPersonID, Region
> HAVING SUM(SalesAmount) >= ALL
> (SELECT SUM(SalesAmount)
> FROM Sales
> GROUP BY SalesPersonID);
>
>Thank you,
>
>David Olsen

--
Message posted via http://www.sqlmonster.com

 | 
Pages: 1
Prev: UPDATE in UDF
Next: Merge membership