Results 1 to 5 of 5

Thread: SQL query help

  1. #1
    Join Date
    Oct 2010
    Posts
    6

    Unanswered: SQL query help

    I am trying to link 2 tables to create the below output. The purpose of the query is to report on the agents who have been able to sell insurance policy with the products we sell within a week timeframe. I am struggling to set up a query to pull back the correct information. Any help would be greatly Appreciated.

    Table 1

    Directorder
    AgentID StartDate Insurance Offered Insurance Sold
    445 05/10/2010 13:15 1 1
    445 07/10/2010 14:27 1 1
    445 07/10/2010 15:26 1 0
    445 07/10/2010 16:30 1 0
    445 06/10/2010 11:32 1 1
    446 05/10/2010 13:15 1 1
    446 07/10/2010 14:27 1 1
    446 07/10/2010 15:26 1 0
    446 07/10/2010 16:30 1 0
    447 05/10/2010 13:15 0 0
    447 07/10/2010 14:27 1 0
    447 07/10/2010 15:26 0 0
    447 07/10/2010 16:30 0 0
    448 05/10/2010 13:15 0 0
    448 07/10/2010 14:27 0 0

    Table 2

    SaleAgent
    Agent Id First Name Surname
    445 Kayleigh Lewis
    446 Dave Jones
    447 John Smith
    448 Dan Williams


    Query output


    First Name Surname Insurance Offered Count Insurnace Sold Count Percentage of Insurnace Sold
    Kayleigh Lewis 5 3 60
    Dave Jones 4 2 50
    John Smith 1 0 0
    Dan Williams 0 0 0

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is a portion of what you're looking for

    i've left something out, for you to do
    Code:
    SELECT SaleAgent.FirstName 
         , SaleAgent.Surname 
         , SUM(Directorder.InsuranceOffered) AS InsuranceOfferedCount  
         , SUM(Directorder.InsuranceSold) AS InsuranceSoldCount
         , SUM(Directorder.InsuranceOffered) * 100.0 /
           SUM(Directorder.InsuranceSold) AS PercentInsuranceSold
      FROM SaleAgent
    INNER
      JOIN Directorder
        ON Directorder.AgentID = SaleAgent.Directorder
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Posts
    6

    Thanks

    Many Thanks for your help.

    I have just one quick question I hope you can help with. When I run the below query and I get a message that reads 'The sum or average aggregate operation cannot take a bit data type as an argument'. Do you know what this means and how I can correct the query.

    Thanks Again

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DanBay View Post
    Do you know what this means and how I can correct the query.
    yes i do, it means you cannot do arithmetic on bit column values

    i think you can fix it like this...
    Code:
    SELECT SaleAgent.FirstName 
         , SaleAgent.Surname 
         , SUM(CASE WHEN Directorder.InsuranceOffered = '1'
                    THEN 1 ELSE NULL END) AS InsuranceOfferedCount
         , ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2010
    Posts
    6
    Many thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •