Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Unanswered: Sorry for such a simple SQL request

    Hi all, I've just started using SQL to access data doing it hands-on with no training. I thought I was doing ok but can't seem to get my head around what I'm sure is a very simple concept. My mentor has headed off on holiday for the week so I thought I'd try you folk...

    Imagine I have a table like this

    tblSALES

    Name Sale_ID Prod_SoldCommission
    A Smith 90001 1 $0
    A Smith 90002 2 $50
    A Smith 90003 1 $50
    B Jones 90004 1 $100
    B Jones 90005 1 $0
    B Jones 90006 1 $0

    and all I want to do is find the total products sold by name, and also the number of commissions paid (not the sum of commissions, but the count where commission is > $0.)

    So I'd return

    Name Sales Commissions
    A Smith 4 2
    B Jones 3 1

    Obviously I can do this in two queries simply enough, but I can't figure out the best way to do it in one go. If someone could write the query to do this so I can refer to it later that would be very helpful.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Obviously I can do this in two queries simply enough,...
    Please publish the two queries.
    Then, the way to combine them might be seen.

  3. #3
    Join Date
    Jul 2012
    Posts
    4
    qry1

    Select name, sum(prod_sold) as Sales
    from tblSales
    group by name


    qry2

    Select name, count(commission) as NumberOfCommissions
    from tblSales
    where commission > 0
    group by name

    I need to replace that where statement with something else... a having or case when clause I guess, but I can't figure it out. Or I need to create a new column and convert the commission into a 1 or 0 variable and just sum the new column with the sum of the sales.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT name
         , SUM(prod_sold) AS Sales
         , COUNT(NULLIF(commission,0)) AS NumberOfCommissions
      FROM tblSales
    GROUP 
        BY name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2012
    Posts
    4
    Thanks very much, that's really helpful....

    .... but I tried using this and I realised what I really wanted was not the count of the commissions, but the sum of the "prod_sold" field when a commission was paid. So back to my original table, which I've tweaked a bit,

    Name Sale_ID Prods_Sold Commission
    A Smith 90001 1 $50
    A Smith 90002 2 $50
    A Smith 90003 1 $0
    B Jones 90004 4 $100
    B Jones 90005 1 $0
    B Jones 90006 1 $0


    the output can contain three fields :
    Name = name
    Total Sales = sum of the product field
    Sales Earning Commissions = sum of product field where commission > 0


    So A Smith would have 4 total sales, 3 earning a commission and
    B Jones would have 6 total sales, 4 earning a commission.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT name
         , SUM(prod_sold) AS TotalSales
         , SUM(CASE WHEN commission > 0
                    THEN prod_sold
                    ELSE NULL 
                END) AS SalesEarningCommissions 
      FROM tblSales
    GROUP 
        BY name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2012
    Posts
    4
    I'd swear on my mother's grave that that is exactly what I tried before I came on here, but obviously I didn't have the syntax quite right.

    Thanks!

Posting Permissions

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