asked on

Help on understanding grouping in windowing functions

Hi Experts,

I'm new to trying the windowing functions and I'm having trouble "grouping" variables the way I need them.

For example with the below, I want to see who are the top vendors billed by region this year.

The problem is that a lot of vendors repeat rows instead of aggregating so I'll see
for Region A:

Vendor X shows up billed $139 then a few rows down $1599...is there a way to force Vendor X to summarize?

VENDOR      REGION      TOTAL_PAY_AMT      SalesCnt      SalesTtl        SalesAvg      SalesPct
x      1      139        123281      53309440.78      432.42      0.01
x      1      1599        123281      53309440.78      432.42      0.01

DECLARE @YR VARCHAR(4)
SET @YR = '2012'

SELECT DISTINCT
VENDOR,
REGION,
TOTAL_PAY_AMT,
COUNT(TOTAL_PAY_AMT) OVER(PARTITION BY REGION) AS SalesCnt,
SUM(TOTAL_PAY_AMT) OVER(PARTITION BY REGION) AS SalesTtl,
AVG(TOTAL_PAY_AMT) OVER(PARTITION BY REGION) AS SalesAvg,
TOTAL_PAY_AMT / SUM(TOTAL_PAY_AMT)
OVER(PARTITION BY REGION) AS SalesPct
FROM
MASTER_CLAIM
WHERE
(REGION IS NOT NULL) AND (DATEPART(YEAR, INV_DT) = @YR)
AND (TOTAL_PAY_AMT > 0) ;

Ess Kay

you can make your query a subquery

DECLARE @YR VARCHAR(4)
SET @YR = '2012'

select
VENDOR,
REGION,
sum(TOTAL_PAY_AMT) as total

from (

SELECT DISTINCT
VENDOR,
REGION,
TOTAL_PAY_AMT,
COUNT(TOTAL_PAY_AMT) OVER(PARTITION BY REGION) AS SalesCnt,
SUM(TOTAL_PAY_AMT) OVER(PARTITION BY REGION) AS SalesTtl,
AVG(TOTAL_PAY_AMT) OVER(PARTITION BY REGION) AS SalesAvg,
TOTAL_PAY_AMT / SUM(TOTAL_PAY_AMT)
OVER(PARTITION BY REGION) AS SalesPct
FROM
MASTER_CLAIM
WHERE
(REGION IS NOT NULL) AND (DATEPART(YEAR, INV_DT) = @YR)
AND (TOTAL_PAY_AMT > 0) ;

)

GROUP BY VENDOR,
REGION

britpopfan74

ASKER

great...but I've the semi-colon is causing an error where it is; I'm trying to figure out where to put it?

Ess Kay

didnt notice

remove it altogether its sql not C++

britpopfan74

ASKER

I keep getting: Incorrect syntax near the keyword 'GROUP'

Ess Kay

ok try this

select VENDOR, REGION, sum(TOTAL_PAY_AMT) as total
from ( --Your old code

SELECT DISTINCT VENDOR, REGION, TOTAL_PAY_AMT,
COUNT(TOTAL_PAY_AMT) OVER(PARTITION BY REGION) AS SalesCnt,
SUM(TOTAL_PAY_AMT) OVER(PARTITION BY REGION) AS SalesTtl,
AVG(TOTAL_PAY_AMT) OVER(PARTITION BY REGION) AS SalesAvg,
TOTAL_PAY_AMT / SUM(TOTAL_PAY_AMT)
OVER(PARTITION BY REGION) AS SalesPct
FROM MASTER_CLAIM
WHERE (REGION IS NOT NULL) AND (DATEPART(YEAR, INV_DT) = @YR) AND (TOTAL_PAY_AMT > 0) )
GROUP BY VENDOR, REGION

Ess Kay

if doesnt work can you send a screenshot of the error

Ess Kay

better yet, maybe its the variable. lets substituite it for this year

select VENDOR, REGION, sum(TOTAL_PAY_AMT) as total
from ( --Your old code

SELECT DISTINCT VENDOR, REGION, TOTAL_PAY_AMT,
COUNT(TOTAL_PAY_AMT) OVER(PARTITION BY REGION) AS SalesCnt,
SUM(TOTAL_PAY_AMT) OVER(PARTITION BY REGION) AS SalesTtl,
AVG(TOTAL_PAY_AMT) OVER(PARTITION BY REGION) AS SalesAvg,
TOTAL_PAY_AMT / SUM(TOTAL_PAY_AMT)
OVER(PARTITION BY REGION) AS SalesPct
FROM MASTER_CLAIM
WHERE (REGION IS NOT NULL) AND (DATEPART(YEAR, INV_DT) = year(getdate())) --###Current year
AND (TOTAL_PAY_AMT > 0) )
GROUP BY VENDOR, REGION

britpopfan74

ASKER

argh...it first gives "Incorrect syntax near ')'." so I remove one of the "(" but then argues "Column 'MASTER_CLAIM.TOTAL_PAY_AMT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

I will keep working around with it...thanks for your input

Ess Kay

the paranthesis look fine

what if you strip it down
and change datepart to year()

try this

select VENDOR, REGION, sum(TOTAL_PAY_AMT) as total_pay
from (

SELECT DISTINCT VENDOR, REGION, TOTAL_PAY_AMT
FROM MASTER_CLAIM
WHERE (REGION IS NOT NULL) AND
YEAR(INV_DT) = year(getdate())
AND (TOTAL_PAY_AMT > 0)

)
GROUP BY VENDOR, REGION

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Ess Kay

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

remove it altogether its sql not C++
Incidentally, semi-colons are part of the ANSI SQL Standard and while in the past they have not been enforced, more and more commands are now requiring a semi-colon (CTE's come to mind). In fact if you read up on BOL, most of the T-SQL examples already use them.

britpopfan74

ASKER

Thanks to you both -- will read up as suggested on syntax