Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Structure Grouping/Averaging


Query Structure Grouping/Averaging

Author
Message
jonathanmreynolds
jonathanmreynolds
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 90
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_DATE MARKET_CODE AVG(E.RATE_AMOUNT)
12-MAR-2009 ADS 187.5
12-MAR-2009 COMP 0
12-MAR-2009 PRO 0
12-MAR-2009 PRO 199
12-MAR-2009 PRO 330
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 PSBY 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 PRO 199
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 CONT 199
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 PRO 330
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 PRO 230
12-MAR-2009 PRO 0
12-MAR-2009 PRO 330
12-MAR-2009 ADS 199
12-MAR-2009 CONT 199
12-MAR-2009 ADS 139.3
12-MAR-2009 ADS 141
12-MAR-2009 ADS 141
12-MAR-2009 PRO 199
12-MAR-2009 PRO 230
12-MAR-2009 ADS 167.14
12-MAR-2009 COMP 0
12-MAR-2009 NLRA 265
12-MAR-2009 ADS 172.5
12-MAR-2009 PKGS 199.99
12-MAR-2009 HSE 199
12-MAR-2009 PRO 230
12-MAR-2009 BAR 330
12-MAR-2009 PRO 230
12-MAR-2009 PRO 250
12-MAR-2009 PRO 230
12-MAR-2009 PRO 199
12-MAR-2009 HSE 0
12-MAR-2009 COMP 0
12-MAR-2009 PRO 230
12-MAR-2009 PRO 230
12-MAR-2009 PRO 230
12-MAR-2009 PRO 330
12-MAR-2009 PRO 249


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.
Brigadur
Brigadur
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 27695
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
Ronald H
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1131 Visits: 611
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_DATE

If there are still bugs, please post the DDL as described in the link below.

Ronald Hensbergen

Help 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
jonathanmreynolds
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 90
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_DATE

If 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


The Dixie Flatline
The Dixie Flatline
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6899
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', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
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', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 199
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
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', 'HSE', 0
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', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
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', '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', 230
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
Mando2
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 107
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"?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search