Post reply

How to use Sum or Average function with joins (Left Join) in select query stored procedure

  • April 22, 2006 at 4:17 am

    #114438

    Hi Friends,

    Please help me in solving this query. I want to use sum function with joins (left join) and where clause in select query or stored procedure.

    I am not able to do it.

    Please give me solution.

    Regards,

    Swapnil.

  • April 22, 2006 at 4:36 am

    #633642

    Hi

    Can you please give somple data and result that you want from that..?

     

     

  • April 24, 2006 at 2:54 am

    #633708

    Hi,

    Following is the sample data :

    1. Company Table

    CompanyID  CompanyName

       1                   Cipla

       2                   Dr. Reddy's

       3                   Infosys

     

    2. Country Table

    CountryID  CountryName

        1                   India

        2                   Japan

        3                   Singapore

     

    3. Topic Table

    TopicID     ProjectID        Topic

       1                 1             Asia - Market Analysis

       2                 1             Asia - Supplier / customer analysis

       3                 2             Quantitative assessment

     

    4. InfoTable

    InfoID    CompanyID         CountryID    TopicID     Rating

      1             1                    2                 1               4

      2             1                    1                 1               5

      3             1                    3                 1               3

      4             2                    1                 2               4

      5             2                    3                 2               3

     

    In above table you can see that, 'CompanyID', 'TopicID' is same but 'Rating' of Topic is different for different Country. What I want is cumulative rating for particular topic.

    Following is the result set, which I want.

     

    Topic                                           Company         CumulativeRating

    Asia - Market Analysis                     Cipla                12

    Asia - Supplier / customer analysis     Dr. Reddy's       7

     

    In above table you can see that,

    1. There is 'Topic' instead of 'TopicID' which you will get from Topic Table

    2. There is CompanyName instead of CompanyID  which you will get from Company Table

    3. In first record CumulativeRating is total of all ratings of 'Asia - Market Analysis' topic.

     

    Following is the stored procedure I am trying to do, but it is giving me error,

     

    Create Proc usp_Summary

    @nProjectID integer,

    As

    Select b.Topic, c.Company, sum(a.Rating) as 'Cumulative Rating'

    from InfoTable a where a.ProjectID=@nProjectID Group by b.Topic

    Left Join Topic b On a.TopicID=b.TopicID

    Left Join Company c on a.CompanyID=c.CompanyID

    I hope you will get it.

     

    Best regards,

     

    Swapnil.

     

  • April 24, 2006 at 3:05 am

    #633713

    Select b.Topic, c.CompanyName, cast(a.Rating as int) as 'Cumulative Rating'

    from InfoTable a

    Left Join Topic b On a.TopicID=b.TopicID

    Left Join Company c on a.CompanyID=c.CompanyID

    --where b.ProjectID=1

    Group by b.Topic,c.CompanyName

  • April 24, 2006 at 3:35 am

    #633719

    Hi Venkat,

    I have tried this but it is giving me following error:

    Column 'c.Company' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Column 'd.Country' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Regards

    Swapnil

  • April 24, 2006 at 3:41 am

    #633720

    Have you actually tried the example Venkat posted? There is no 'company' nor 'country' in that example.

    If your actual columnnames are different, then change accordingly to how your columns are named.

    /Kenneth

  • April 24, 2006 at 3:59 am

    #633722

    Yes I have tried the example posted by Venkat. But it is still giving me error. Following is the code posted by Venakat

    Select b.Topic, c.CompanyName, cast(a.Rating as int) as 'Cumulative Rating'

    from InfoTable a

    Left Join Topic b On a.TopicID=b.TopicID

    Left Join Company c on a.CompanyID=c.CompanyID

    --where b.ProjectID=1

    Group by b.Topic,c.CompanyName

    And the following is the error I am getting-

    Column 'c.Company' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Column 'a.Rating' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    What I want is, the sum of all ratings of particular topic of particular Project.

    If you see the sample data which I had given earlier, you will get clear idea about this problem.

    Best Regards,

    Swapnil

  • April 24, 2006 at 4:09 am

    #633724

    Hi Kenneth,

    I have tried the way Venkat posted, and also I have change the columns names according to my table in database, but still it is giving me error of GroupBy clause.

    Thanks.

  • April 24, 2006 at 5:23 am

    #633732

    Looks like the sum was missed off the a.Rating to get a cumulative rating - looks like a typo!

    Select b.Topic, c.CompanyName, sum(cast(a.Rating as int)) as 'Cumulative Rating'

    from InfoTable a

    Left Join Topic b On a.TopicID=b.TopicID

    Left Join Company c on a.CompanyID=c.CompanyID

    --where b.ProjectID=1

    Group by b.Topic,c.CompanyName

  • April 24, 2006 at 5:38 am

    #633734

    Thanks all of you,

    Thanks for helping me out!

    Regards,

    Swapnil.

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

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