Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Development » Query Structure Grouping/Averaging
Add to briefcase

Query Structure Grouping/Averaging Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
jonathanmreynolds
Posted Thursday, March 12, 2009 8:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 09, 2011 9:02 PM
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.
Post #674892
Brigadur
Posted Friday, March 13, 2009 4:42 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:06 PM
Points: 576, Visits: 27,681
Hi, I don't understand your Problem #2 question, regarding Problem #1, I think you should remove RN.BEGIN_DATE from the select.
Post #675029
Ronald H
Posted Friday, March 13, 2009 5:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 30, 2012 1:52 AM
Points: 1,116, Visits: 602
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
Post #675049
jonathanmreynolds
Posted Friday, March 13, 2009 10:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 09, 2011 9:02 PM
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
Post #675425
The Dixie Flatline
Posted Friday, March 13, 2009 10:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 3,790, Visits: 5,548
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? -- Stephen Stills
Post #675770
Mando2
Posted Sunday, March 15, 2009 1:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 13, 2010 2:54 PM
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"?


Post #676167
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.