Thread: COUNTing issue

Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep Power
    0

    COUNTing issue


    Hi,

    I'm trying to do some reporting on a DB. It is based on some basic invoicing and there are 2 ways on invoicing in the system. I will do a join to combine the two sets of results. For now, the problem I'm having with it is as follows: the way to identify the first type of invoicing is the fact that there will be one and one only of a particular product attached to the main object. So I want to gete some totals from other tables based on that criteria. However, the count of the related products is not working.

    Code:
    SELECT        dbo.Customer.CustomerCode, dbo.Sales.SNo, dbo.Sales.InvoiceableQuantity
    FROM            dbo.Sales INNER JOIN
    dbo.SalesProduct ON dbo.Sales.SalesID = dbo.SalesProduct.SalesID INNER JOIN
    dbo.Customer ON dbo.Sales.CustomerID = dbo.Customer.CustomerID LEFT OUTER JOIN
                             dbo.Product ON dbo.Product.ProductID = dbo.SalesProduct.ProductTypeID
    WHERE        (COUNT(dbo.SalesProduct.ProductTypeID) = 1)
    The above gives me an error relating to HAVING clauses for aggregates. I would have thought this was straight forward especially since I'm not using any grouping.

    There are proper relationships between sales and sales product, salesproduct and product, and sales and customer.

    Any ideas? really appreciate any help.
  2. #2
  3. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,959
    Rep Power
    4035
    In order to use an aggregate function such as COUNT() you have to include a GROUP BY clause in your query. You also can't use an aggregate function in the WHERE clause like you are, it needs to be moved to a HAVING clause.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep Power
    0
    Originally Posted by kicken
    In order to use an aggregate function such as COUNT() you have to include a GROUP BY clause in your query. You also can't use an aggregate function in the WHERE clause like you are, it needs to be moved to a HAVING clause.
    Thanks - I've done that and I'm getting that piece to work - it returns the single test object that I would expect. Where it's falling down for me now (and this is part of a bigger query) is when I add the other criteria (that the only product would be a certain product attached to the sales item) in the having clause it returns 3 items, not 1. So with the count criteria it returns 1 and with the product check it returns 3. It seems to be ignoring the count for some reason. Why would it do that?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by kloud
    Why would it do that?
    not sure

    any chance you could show us the actual query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep Power
    0
    Originally Posted by r937
    not sure

    any chance you could show us the actual query?
    Code:
    SELECT dbo.Customer.CustomerCode, dbo.Sales.SalesNo, dbo.Sales.InvoiceableQuantity
    FROM            dbo.Sales INNER JOIN
                             dbo.SalesProduct ON dbo.Sales.SalesID = dbo.SalesProduct.SalesID INNER JOIN
                             dbo.Customer ON dbo.Sales.CustomerID = dbo.Customer.CustomerID LEFT OUTER JOIN
                             dbo.Product ON dbo.Product.ProductID = dbo.SalesProduct.ProductTypeID
    GROUP BY dbo.Customer.CustomerCode, dbo.Sales.SalesNo, dbo.Sales.InvoiceableQuantity
    HAVING        (COUNT(dbo.SalesProduct.ProductTypeID) = 1)
    Query above. I need to add a stipulation in there that as well as the number of products in the sale being 1, that the product would be a particular product - product.code = 'TT' (relationship is salesproduct.producttypeID = product.productid)

    Thanks,
    Tom.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Code:
    SELECT dbo.Customer.CustomerCode
         , dbo.Sales.SalesNo
         , dbo.Sales.InvoiceableQuantity
      FROM dbo.Sales 
    INNER 
      JOIN dbo.Customer 
        ON dbo.Customer.CustomerID = dbo.Sales.CustomerID   
    INNER 
      JOIN dbo.SalesProduct 
        ON dbo.SalesProduct.SalesID  = dbo.Sales.SalesID 
    INNER 
      JOIN dbo.Product 
        ON dbo.Product.ProductID = dbo.SalesProduct.ProductTypeID
       AND dbo.Product.code = 'TT' 
    GROUP  
        BY dbo.Customer.CustomerCode
         , dbo.Sales.SalesNo
         , dbo.Sales.InvoiceableQuantity
    HAVING COUNT(dbo.SalesProduct.ProductTypeID) = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep Power
    0
    Originally Posted by r937
    Code:
    SELECT dbo.Customer.CustomerCode
         , dbo.Sales.SalesNo
         , dbo.Sales.InvoiceableQuantity
      FROM dbo.Sales 
    INNER 
      JOIN dbo.Customer 
        ON dbo.Customer.CustomerID = dbo.Sales.CustomerID   
    INNER 
      JOIN dbo.SalesProduct 
        ON dbo.SalesProduct.SalesID  = dbo.Sales.SalesID 
    INNER 
      JOIN dbo.Product 
        ON dbo.Product.ProductID = dbo.SalesProduct.ProductTypeID
       AND dbo.Product.code = 'TT' 
    GROUP  
        BY dbo.Customer.CustomerCode
         , dbo.Sales.SalesNo
         , dbo.Sales.InvoiceableQuantity
    HAVING COUNT(dbo.SalesProduct.ProductTypeID) = 1
    I've moved the database on a bit and changed a few table names but what you gave me there does what I need. I need to use that query with a SUM on quantities that are processed so that we can assess what has been delivered, invoiced and is not yet invoiced.

    The sales table and the line items are not directly related (with a key) but each line item (and there's only one per invoice) contains a reference to the ID of the sale involved. I'm using that to join to get information from invoices included and it seems to be working. When I add a sum on quantities then it simply gives the quantity for each line item because obviously when you're grouping by line item it will only give the value related to that one item. When I take out the lineitemID reference the query returns nothing.

    SELECT dbo.SalesProposal.SalesProposalID, dbo.InvoiceItem.InvoiceItemID, dbo.SalesProposal.SPNo, dbo.Customer.CustomerCode, SUM(dbo.InvoiceItem.Quantity)
    AS Expr1
    FROM dbo.Invoice INNER JOIN
    dbo.InvoiceItem ON dbo.Invoice.InvoiceID = dbo.InvoiceItem.InvoiceID INNER JOIN
    dbo.SalesProposal ON dbo.SalesProposal.SalesProposalID = dbo.InvoiceItem.ItemReferenceID INNER JOIN
    dbo.Product ON dbo.InvoiceItem.ProductID = dbo.Product.ProductID AND dbo.Product.Code = 'TT' INNER JOIN
    dbo.Customer ON dbo.SalesProposal.CustomerID = dbo.Customer.CustomerID INNER JOIN
    dbo.SalesProposalProduct ON dbo.SalesProposal.SalesProposalID = dbo.SalesProposalProduct.SalesProposalID
    GROUP BY dbo.SalesProposal.SalesProposalID, dbo.InvoiceItem.InvoiceItemID, dbo.SalesProposal.SPNo, dbo.Customer.CustomerCode
    HAVING (COUNT(dbo.SalesProposalProduct.ProductTypeID) = 1)

    So from the query above it returns the colums including the quantity but if I take out the lineitemid in the select statement it returns nothing. Any ideas on this?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    returns nothing? or fails on a syntax error?

    you can remove a column from both the SELECT list and the GROUP BY list, or from the SELECT list only, but you can't remove it from the GROUP BY list only, as that's a syntax error

    also, obviously, as there is only one line item per invoice, if you fail to GROUP BY the line item, then there will be more than one line item in each group, so the HAVING clause rejects them all, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep Power
    0
    Originally Posted by r937
    returns nothing? or fails on a syntax error?

    you can remove a column from both the SELECT list and the GROUP BY list, or from the SELECT list only, but you can't remove it from the GROUP BY list only, as that's a syntax error

    Also, obviously, as there is only one line item per invoice, if you fail to GROUP BY the line item, then there will be more than one line item in each group, so the HAVING clause rejects them all, right?
    Just to clarify, if I remove from the select and group by, meaning I don't want it in the query at all. I don't need it on the output (report) and if it's in the group by it obviously affects the results. The column is InvoiceItem, not Lineitem as I said in my last. So, it just returns nothing.

    The having clause filters based on the number of products attached to the sales proposal, not the invoice. The way the system works is that products are attached to sales proposals and when generating the invoice an line item is added with that product, the quantity and the cost. This is just for one invoicing method that's used hence the stipulation that the product TT be the only one on that sales proposal. Sales proposals with other setups are handled differently for invoicing.
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by kloud
    The having clause filters based on the number of products attached to the sales proposal, not the invoice.
    um, not quite

    the COUNT in the HAVING clause counts the number of non-null values in the dbo.SalesProposalProduct.ProductTypeID column in the joined and grouped results

    maybe you wanted to add DISTINCT into the count?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep Power
    0
    Originally Posted by r937
    um, not quite

    the COUNT in the HAVING clause counts the number of non-null values in the dbo.SalesProposalProduct.ProductTypeID column in the joined and grouped results

    maybe you wanted to add DISTINCT into the count?
    Thanks for the clarification. The gist of what I want to achieve is I want to get the total quantity invoiced for sales proposals which have one product linked to them with the code TT. In the invoiceitems, item reference refers to the ID of the sales proposal for that line item. So when grouped by salesproposal I want to total all the quantities where the itemreference matches the any sales proposal where there is 1 product and it's code it TT. Simples
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep Power
    0
    Hi,

    THe schema has moved on another bit. I'm still having some issues with the grouping on this report. This is the current query

    Code:
    SELECT        dbo.Customer.CustomerCode, dbo.SalesProposal.SPNo AS SalesProposal, 
    CASE WHEN dbo.SalesProposal.PreDispatchInvoicing = 1 THEN 'Pre Dispatch' ELSE 'Post Dispatch' END AS Invoicing,
    CASE WHEN SUM(dbo.InvoiceItem.Quantity) IS NULL THEN 0 ELSE SUM(dbo.InvoiceItem.Quantity) END AS InvoicedQuantity,
    CASE WHEN SUM(dbo.Dispatch.Weight * dbo.Dispatch.ConversionFactor) IS NULL THEN 0 ELSE SUM(dbo.Dispatch.Weight * dbo.Dispatch.ConversionFactor) END AS DeliveredQuantity,
    SUM(CASE WHEN dbo.SalesProposal.PreDispatchInvoicing = 1 THEN (dbo.SalesProposal.ForecastQuantity - dbo.InvoiceItem.Quantity) ELSE ((dbo.Dispatch.Weight * dbo.Dispatch.ConversionFactor) 
    - dbo.InvoiceItem.Quantity) END)
                             AS InvoiceableQuantity
    FROM            dbo.SalesProposal INNER JOIN
                             dbo.SalesProposalStatus ON dbo.SalesProposal.StatusID = dbo.SalesProposalStatus.SalesProposalStatusID INNER JOIN
                             dbo.Customer ON dbo.SalesProposal.CustomerID = dbo.Customer.CustomerID LEFT OUTER JOIN
                             dbo.Dispatch ON dbo.Dispatch.SalesProposalID = dbo.SalesProposal.SalesProposalID LEFT OUTER JOIN
                             dbo.Invoice ON dbo.Invoice.SalesProposalID = dbo.SalesProposal.SalesProposalID LEFT OUTER JOIN
                             dbo.InvoiceItem ON dbo.InvoiceItem.InvoiceID = dbo.Invoice.InvoiceID
    WHERE        (dbo.SalesProposalStatus.SalesProposalStatus = 'Sold')
    GROUP BY dbo.Customer.CustomerCode, dbo.SalesProposal.SPNo,
                             CASE WHEN dbo.SalesProposal.PreDispatchInvoicing = 1 THEN 'Pre Dispatch' ELSE 'Post Dispatch' END
    SalesProposals are basically sales invoices. Dispatches are deliveries made from the sales proposals. Invoices and InvoiceItems are self explanatory as is Customer.

    When I run the above query I'm getting weird results. The value for InvoicedQuantity is coming in as 3 times what it should be for one sales proposal and the value for invoiceable is coming in with a value that I can't see where it getes it from. There are currently 5 sales proposals in the test DB, filtered down to 3 by the status filter above. There are only invoices for 2 of them.

    Anybody see anything obviously wrong with this?

    T.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep Power
    0
    I have an image of the schema if anybody wants to see it but not sure how to post it on this forum.

    T.
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep Power
    0
    Looking like a need to use derived tables because some of the joins are adding on unnecesary rows. In terms of a query this size has anybody any suggestions on adding derived tables to get the right result.

    Thanks.
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by kloud
    In terms of a query this size has anybody any suggestions on adding derived tables to get the right result.
    this isn't all that big of a query

    as far as results "coming in as 3 times what it should be" this is a clear sign of cross join effects and you're right, using derived tables which reduce one-to-many results down to one-to-one is likely the way to go

    can you please identify thge cardinality for all your relationships

    e.g.
    each SalesProposal has ____ SalesProposalStatus rows
    each SalesProposal has ____ Customer rows
    each SalesProposal has ____ Dispatch rows
    each SalesProposal has ____ Invoice rows
    each Invoice has multiple InvoiceItem rows

    now if there are several one-to-many relationships involved, you will also have to indeicate which ones should be collapsed by a derived table, and exactly how to do that (using MIN, SUM, AVG, or whatever)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo