Aggregate Query help

  • I am attempting to wrie a qury that will return aggregate totals from two different tables. The problem is that the TotalForecast totals are way to high.

    How do I write a query to obtain the correct totals?

    Table 1 - dbo.QM_Results

    Columns - dbo.QM_Results.Special8, dbo.QM_Results.SessionName, dbo.QM_Results.PersonNumber

    Table 2 - dbo.PM_ForecastView

    Columns - dbo.PM_ForecastView.Hierarchy, dbo.PM_ForecastView.Forecast

    Select substring(dbo.QM_Results.Special8,0,6) AS Hierarchy,

    substring(dbo.QM_Results.SessionName,0,11) As CourseCode,

    count(dbo.QM_Results.PersonNumber) TotalAssociates,

    sum(dbo.PM_ForecastView.Forecast) TotalForecast

    From dbo.QM_Results INNER JOIN dbo.PM_ForecastView

    ON dbo.PM_ForecastView.Hierarchy = substring(dbo.QM_Results.Special8,0,6)

    where SessionMid in ('96882139', '23620891', '45077427', '29721437')

    AND substring(dbo.QM_Results.Special8,0,6) in ('EZHBA')

    Group By substring(dbo.QM_Results.Special8,0,6),

    substring(dbo.QM_Results.SessionName,0,11)

    Sample of data returned with my current query.

    HierarchyCourseCodeTotalAssociatesTotalForecast

    EZHBACARD1672001179 141480

    EZHBACARD1672011416 169920

    EZHBACARD1672021119 134280

    EZHBACARD16720499 11880

    Results when I run aggregate query separately

    Actual Total taken

    Hierarchy CourseCodeTotalTaken

    EZHBA CARD167200393

    EZHBA CARD167201472

    EZHBA CARD167202373

    EZHBA CARD16720433

    Forecasted Total taken

    HierarchyCourseCodeForecast

    EZHBACARD167200999

    EZHBACARD167201900

    EZHBACARD167202800

    EZHBACARD167204800

  • The join condition gives you everything in the second table matched on Hierarchy and does not take into account the coursecode.

    so for every sessionname record in your qm_results table you get added in the count and sum of all the records in your pm_forecastview table.

    Here is an example skinnied down...

    Declare @QM_Results table

    (Special8 varchar(8), SessionName varchar(11), PersonNumber INT)

    Declare @PM_ForecastView table

    (Hierarchy varchar(6), Forecast int)

    insert into @qm_results

    select 'ezhba', 'CARD167200', 2 Union all -- 2 records for 1st class

    select 'ezhba', 'CARD167200', 1 Union all

    select 'ezhba', 'CARD167201', 2 Union all -- 3 records for 2nd class

    select 'ezhba', 'CARD167201', 1 Union all

    select 'ezhba', 'CARD167201', 3 Union all

    select 'ezhba', 'CARD167202', 2 Union all -- 1 record for 3rd class

    select 'ezhba', 'CARD167204', 3 -- 1 record for 4th class

    insert into @PM_ForecastView

    select 'ezhba', 2 Union all

    select 'ezhba', 2 Union all

    select 'ezhba', 2 Union all

    select 'ezhba', 2

    Select substring(Special8,0,6) AS Hrarchy,

    substring(SessionName,0,11) As CourseCode,

    count(PersonNumber) TotalAssociates,

    sum(Forecast) TotalForecast

    From @QM_Results inner JOIN @PM_ForecastView

    ON Hierarchy = substring(Special8,0,6)

    Group By substring(Special8,0,6),

    substring(SessionName,0,11)

    -----------------------------------

    ----------------- results ------------------

    ----------------------------------------------

    Hrarchy CourseCode TotalAssociates TotalForecast

    ------- ----------- --------------- -------------

    ezhba CARD167200 8 16

    ezhba CARD167201 12 24

    ezhba CARD167202 4 8

    ezhba CARD167204 4 8

    is there another way to link the records in the forecast table to the coursecode? If so, add that to the JOIN condition.

    Let me know if this helps.

    Toni

  • Yes there is a field in the forcast table for course code.

    dbo.PM_ForecastView.CourseCode. What would the additional join statement look like?

    I am looking at your code and I am trying to understand how it works. Do I just need to replace the @QM_Results table and @PM_ForecastView table with my existing tables?

  • Other than creating test tables and data, I just trimmed your code and did not change any logic. The table names are not relevant.

    The only part of your code to change is the Join condition by adding an additional condition to the ON portion. The rest of the code can be left alone as far as I can tell - I really can not follow the purpose but assume you also have trimmed out the code and tables.

    From dbo.QM_Results

    INNER JOIN dbo.PM_ForecastView

    ON dbo.PM_ForecastView.Hierarchy = substring(dbo.QM_Results.Special8,0,6)

    AND dbo.PM_ForecastView.CourseCode = substring(dbo.QM_Results.SessionName,0,11)

    This way you will be able to aggregate the results by coursecode and hierarchy.

    Toni

  • Perhaps if you could supply some test data to be sure I am addressing the right problem that would help.

    It would be good to see what you are actually trying to accomplish and how (in a description as well as the code).

    There is a possibility that the JOIN of the tables might not make logical sense.

    Thanks

  • I've attached sample data for each table view.

    I just want to return

    Hierarchy, -- From Forecast table

    Course Code, -- From Forecast Table

    Total forecast, -- From Forecast table

    TotalActualAtteneded -- From PM_QMDetails table. Count(PersonNumber)

  • Here is a sample of the results that I want. I want to combine the results of these two querries into one query.

    This is query 1

    Select Hierarchy,

    CourseCode,

    count(PersonNumber) TotalTaken

    From dbo.PM_QMDetails

    where Hierarchy = 'EZHBA'

    Group By CourseCode,Hierarchy

    [/CODE]

    Results:

    HierarchyCourseCodeTotalTaken

    EZHBACARD167200393

    EZHBACARD167201472

    EZHBACARD167202373

    EZHBACARD16720433

    Qurey 2

    Select dbo.PM_Hierarchy.Hierarchy,

    dbo.PM_CourseCodes.CourseCode,

    sum(dbo.PM_Forecast.ParticipantForcast) Forecast

    From dbo.PM_Forecast

    LEFT JOIN dbo.PM_Hierarchy ON dbo.PM_Forecast.Hierarchy = dbo.PM_Hierarchy.ID

    LEFT JOIN dbo.PM_CourseCodes ON dbo.PM_CourseCodes.ID = dbo.PM_Forecast.CourseCode

    Where dbo.PM_Hierarchy.Hierarchy = 'EZHBA'

    group By dbo.PM_Hierarchy.Hierarchy,

    dbo.PM_CourseCodes.CourseCode

    Results

    HierarchyCourseCodeForecast

    EZHBACARD167200999

    EZHBACARD167201900

    EZHBACARD167202800

    EZHBACARD167204800

    Desired Results

    HierarchyCourseCodeTotalTaken TotalForecast

    EZHBACARD167200393 999

    EZHBACARD167201472 900

    EZHBACARD167202373 800

    EZHBACARD16720433 800

    Thank you for all of your help!

  • Ok.. maybe someone else can verify and/or adjust my observation but it looks like you are joining two tables with many-to-many relationship and getting lots of duplicate data.

    This might not be the prettiest way to get your results but it does combine them into one query and eliminates the odd results from the JOIN.

    Reusing my test data....

    Declare @QM_R table

    (Hrarchy varchar(8), SessionName varchar(11), PersonNumber INT)

    Declare @PM_F table

    (Hierarchy varchar(6), coursecode varchar(11), Forecast int)

    insert into @qm_r

    select 'ezhba', 'CARD167200', 2 Union all -- 2 records for 1st class

    select 'ezhba', 'CARD167200', 1 Union all

    select 'ezhba', 'CARD167201', 2 Union all -- 3 records for 2nd class

    select 'ezhba', 'CARD167201', 1 Union all

    select 'ezhba', 'CARD167201', 3 Union all

    select 'ezhba', 'CARD167202', 2

    insert into @PM_F

    select 'ezhba', 'CARD167200', 1 Union all

    select 'ezhba', 'CARD167200', 1 Union all

    select 'ezhba', 'CARD167201', 1 Union all

    select 'ezhba', 'card167202', 3 union all

    select 'ezhba', 'CARD167202', 1

    select distinct hrarchy,coursecode, (select count(personnumber) from @qm_r

    where hierarchy = hrarchy and coursecode=sessionname) totaltaken,

    (select sum(forecast) from @PM_F

    where hierarchy = hrarchy and coursecode=sessionname) totalforecast

    from @pm_f,@qm_R

    Where hierarchy = hrarchy and coursecode = sessionname

    My best guess at what the table and field names are - your query would be:

    Select distinct dbo.PM_ForecastView.Hierarchy, -- hierarchy

    dbo.PM_ForecastView.CourseCode, -- Course Code

    (select sum(dbo.PM_ForecastView.Forecast) -- total forecast for hierarchy/coursecode

    from dbo.PM_ForecastView

    where dbo.PM_ForecastView.Hierarchy = dbo.QM_Results.hierarchy and

    dbo.PM_ForecastView.Coursecode= dbo.QM_Results.CourseCode) totalforecast,

    (select count(dbo.QM_Results.personnumber) -- total Persons for hierarchy/coursecode

    from dbo.QM_Results

    where dbo.PM_ForecastView.Hierarchy = dbo.QM_Results.hierarchy and

    dbo.PM_ForecastView.Coursecode= dbo.QM_Results.CourseCode) totaltaken

    From dbo.PM_ForecastView, dbo.QM_Results

    where dbo.PM_ForecastView.Hierarchy = dbo.QM_Results.hierarchy and

    dbo.PM_ForecastView.Coursecode= dbo.QM_Results.CourseCode

  • That is exactly what I needed. Thanks

  • Glad I could help

Viewing 10 posts - 1 through 9 (of 9 total)

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