Query Structure Grouping/Averaging

Question

Query Structure Grouping/Averaging

jonathanmreynolds

SSC Eights!

Points: 955
More actions
March 12, 2009 at 8:25 pm

#384122

Hello, i'm writing this query:
SELECT RN.BEGIN_DATE, E.MARKET_CODE, AVG(E.RATE_AMOUNT)
FROM RESERVATION_DAILY_ELEMENT_NAME N, RESERVATION_DAILY_ELEMENTS E, RESERVATION_NAME RN, MARKET_CODES_TEMPLATE M
WHERE TRUNC(RN.BEGIN_DATE) BETWEEN TRUNC(PMS_P.BUSINESS_DATE) AND TRUNC(PMS_P.BUSINESS_DATE +1)
AND RN.RESV_NAME_ID = N.RESV_NAME_ID
AND N.RESV_DAILY_EL_SEQ = E.RESV_DAILY_EL_SEQ
AND E.MARKET_CODE = M.MARKET_CODE
GROUP BY RN.BEGIN_DATE, E.MARKET_CODE, E.RATE_AMOUNT
ORDER BY RN.BEGIN_DATE
and it returns this result set:
BEGIN_DATEMARKET_CODEAVG(E.RATE_AMOUNT)
12-MAR-2009ADS187.5
12-MAR-2009COMP0
12-MAR-2009PRO0
12-MAR-2009PRO199
12-MAR-2009PRO330
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009PSBY0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009PRO199
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009CONT199
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009PRO330
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009HSE0
12-MAR-2009PRO230
12-MAR-2009PRO0
12-MAR-2009PRO330
12-MAR-2009ADS199
12-MAR-2009CONT199
12-MAR-2009ADS139.3
12-MAR-2009ADS141
12-MAR-2009ADS141
12-MAR-2009PRO199
12-MAR-2009PRO230
12-MAR-2009ADS167.14
12-MAR-2009COMP0
12-MAR-2009NLRA265
12-MAR-2009ADS172.5
12-MAR-2009PKGS199.99
12-MAR-2009HSE199
12-MAR-2009PRO230
12-MAR-2009BAR330
12-MAR-2009PRO230
12-MAR-2009PRO250
12-MAR-2009PRO230
12-MAR-2009PRO199
12-MAR-2009HSE0
12-MAR-2009COMP0
12-MAR-2009PRO230
12-MAR-2009PRO230
12-MAR-2009PRO230
12-MAR-2009PRO330
12-MAR-2009PRO249
Problem #1
I want to average the rate amount per day per market code, and here you see i'm getting a detailed breakout of each individual record associated with the market_code for this date.
Problem #2
I'm trying to query this so that I get every "market_code" regardless whether it has been used. The table "market_codes_template" is a list of all configured "market_codes".
Can anyone offer a suggestion?
thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Brigadur SSCrazy Points: 2097 More actions · Answer 1

Hi, I don't understand your Problem #2 question, regarding Problem #1, I think you should remove RN.BEGIN_DATE from the select.

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 2

The way your query is written is possible and works. However, it's not the most versatile. I would suggest to you, you join tables using INNER/OUTER/FULL JOIN. I rewrote your query and changed it that way, that it solved your problems:SELECT RN.BEGIN_DATE, M.MARKET_CODE, AVG(E.RATE_AMOUNT)

FROM RESERVATION_DAILY_ELEMENT_NAME N

INNER JOIN RESERVATION_DAILY_ELEMENTS E ON N.RESV_DAILY_EL_SEQ = E.RESV_DAILY_EL_SEQ

INNER JOIN RESERVATION_NAME RN ON RN.RESV_NAME_ID = N.RESV_NAME_ID

RIGHT OUTER JOIN MARKET_CODES_TEMPLATE M ON E.MARKET_CODE = M.MARKET_CODE

WHERE TRUNC(RN.BEGIN_DATE) BETWEEN TRUNC(PMS_P.BUSINESS_DATE) AND TRUNC(PMS_P.BUSINESS_DATE +1)

GROUP BY RN.BEGIN_DATE, E.MARKET_CODE

ORDER BY RN.BEGIN_DATEIf there are still bugs, please post the DDL as described in the link below.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

jonathanmreynolds SSC Eights! Points: 955 More actions · Answer 3

r.hensbergen (3/13/2009)
The way your query is written is possible and works. However, it's not the most versatile. I would suggest to you, you join tables using INNER/OUTER/FULL JOIN. I rewrote your query and changed it that way, that it solved your problems:SELECT RN.BEGIN_DATE, M.MARKET_CODE, AVG(E.RATE_AMOUNT) FROM RESERVATION_DAILY_ELEMENT_NAME N INNER JOIN RESERVATION_DAILY_ELEMENTS E ON N.RESV_DAILY_EL_SEQ = E.RESV_DAILY_EL_SEQ INNER JOIN RESERVATION_NAME RN ON RN.RESV_NAME_ID = N.RESV_NAME_ID RIGHT OUTER JOIN MARKET_CODES_TEMPLATE M ON E.MARKET_CODE = M.MARKET_CODE WHERE TRUNC(RN.BEGIN_DATE) BETWEEN TRUNC(PMS_P.BUSINESS_DATE) AND TRUNC(PMS_P.BUSINESS_DATE +1) GROUP BY RN.BEGIN_DATE, E.MARKET_CODE
ORDER BY RN.BEGIN_DATEIf there are still bugs, please post the DDL as described in the link below.

Thank you, much cleaner this way. The data still comes back for each row containing that market code for the given date.

I believe I would need to actually SUM and then AVG e.RATE_AMOUNT by Market Code by day. Problem is I have no idea how to write that.

Ultimately would like to see it like this:

DATE MARKET AVERAGE RATE

021209 PRO 269.34

021209 BAR 349.98

021209 HSE 0.00

021209 COMP 0.00

[/CODE]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

There are ways to take sums and then to take averages of those sums, but they usually involve different groupings. For example I could take the sum of measurements for a day to produce a daily total, which I could then average over a month to give me an average daily total for the month. Is that what you want to do?

I'm missing something in your question, because looking at your data sample, I can't seem to find the desired results.

declare @sample table (BEGIN_DATE datetime, MARKET_CODE varchar(5), RATE_AMOUNT numeric(5,2))

insert into @sample

SELECT '12-MAR-2009', 'ADS', 187.5

UNION ALL SELECT '12-MAR-2009', 'COMP', 0

UNION ALL SELECT '12-MAR-2009', 'PRO', 0

UNION ALL SELECT '12-MAR-2009', 'PRO', 199

UNION ALL SELECT '12-MAR-2009', 'PRO', 330

UNION ALL SELECT '12-MAR-2009', 'HSE', 0

UNION ALL SELECT '12-MAR-2009', 'PSBY', 0

UNION ALL SELECT '12-MAR-2009', 'HSE', 0

UNION ALL SELECT '12-MAR-2009', 'PRO', 199

UNION ALL SELECT '12-MAR-2009', 'HSE', 0

UNION ALL SELECT '12-MAR-2009', 'CONT', 199

UNION ALL SELECT '12-MAR-2009', 'HSE', 0

UNION ALL SELECT '12-MAR-2009', 'PRO', 330

UNION ALL SELECT '12-MAR-2009', 'HSE', 0

UNION ALL SELECT '12-MAR-2009', 'PRO', 230

UNION ALL SELECT '12-MAR-2009', 'PRO', 0

UNION ALL SELECT '12-MAR-2009', 'PRO', 330

UNION ALL SELECT '12-MAR-2009', 'ADS', 199

UNION ALL SELECT '12-MAR-2009', 'CONT', 199

UNION ALL SELECT '12-MAR-2009', 'ADS', 139.3

UNION ALL SELECT '12-MAR-2009', 'ADS', 141

UNION ALL SELECT '12-MAR-2009', 'PRO', 199

UNION ALL SELECT '12-MAR-2009', 'PRO', 230

UNION ALL SELECT '12-MAR-2009', 'ADS', 167.14

UNION ALL SELECT '12-MAR-2009', 'COMP', 0

UNION ALL SELECT '12-MAR-2009', 'NLRA', 265

UNION ALL SELECT '12-MAR-2009', 'ADS', 172.5

UNION ALL SELECT '12-MAR-2009', 'PKGS', 199.99

UNION ALL SELECT '12-MAR-2009', 'HSE', 199

UNION ALL SELECT '12-MAR-2009', 'PRO', 230

UNION ALL SELECT '12-MAR-2009', 'BAR', 330

UNION ALL SELECT '12-MAR-2009', 'PRO', 230

UNION ALL SELECT '12-MAR-2009', 'PRO', 250

UNION ALL SELECT '12-MAR-2009', 'PRO', 230

UNION ALL SELECT '12-MAR-2009', 'PRO', 199

UNION ALL SELECT '12-MAR-2009', 'HSE', 0

UNION ALL SELECT '12-MAR-2009', 'COMP', 0

UNION ALL SELECT '12-MAR-2009', 'PRO', 230

UNION ALL SELECT '12-MAR-2009', 'PRO', 330

UNION ALL SELECT '12-MAR-2009', 'PRO', 249

select begin_date,market_code,sum(rate_amount) as sumRate,cast(avg(rate_amount) as numeric(5,2)) as avgRate

from @sample

group by begin_date,market_code

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Mando2 SSChasing Mays Points: 609 More actions · Answer 5

Thank you, much cleaner this way. The data still comes back for each row containing that market code for the given date.

1. Can you post the resultset for this?

2. From you description here, it appears your problem definition may not be complete. Can you post your DDL and sample dataset for each of the Objects?

3. Finally, just to confirm - are you looking for a different DATE "format"?