Query Structure Grouping/Averaging

  • 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.

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

  • 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

  • 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]

  • 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

  • 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"?

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

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