COUNTing issue

Dev Shed Forums Sponsor:

February 14th, 2013, 02:50 AM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation 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.

February 14th, 2013, 04:25 AM

kicken

Wiser? Not exactly.

Dev Shed God 1st Plane (5500 - 5999 posts)

Join Date: May 2001

Location: Bonita Springs, FL

Posts: 5,654

Folding Points: 2670569 Folding Title: Super Ultimate Folder - Level 6

Time spent in forums: 2 Months 2 Weeks 2 Days 5 h 1 m 44 sec
Reputation Power: 3436

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

Spidermonkey Tutorial;

If I helped out out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud

February 14th, 2013, 12:39 PM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation Power: 0

Quote:

Originally Posted by kicken

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?

February 14th, 2013, 01:25 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,380

Time spent in forums: 3 Months 1 Week 2 Days 7 h 54 m 19 sec
Reputation Power: 4140

Quote:

Originally Posted by kloud

Why would it do that?

not sure

any chance you could show us the actual query?

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

February 14th, 2013, 02:38 PM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation Power: 0

Quote:

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.

February 14th, 2013, 06:03 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,380

Time spent in forums: 3 Months 1 Week 2 Days 7 h 54 m 19 sec
Reputation Power: 4140

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

February 28th, 2013, 07:23 AM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation Power: 0

Quote:

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?

February 28th, 2013, 08:03 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,380

Time spent in forums: 3 Months 1 Week 2 Days 7 h 54 m 19 sec
Reputation Power: 4140

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?

February 28th, 2013, 08:22 AM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation Power: 0

Quote:

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.

#10

February 28th, 2013, 11:16 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,380

Time spent in forums: 3 Months 1 Week 2 Days 7 h 54 m 19 sec
Reputation Power: 4140

Quote:

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?

#11

February 28th, 2013, 03:57 PM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation Power: 0

Quote:

Originally Posted by r937

um, not quite

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

#12

March 8th, 2013, 05:34 AM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation 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.

#13

March 8th, 2013, 05:35 AM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation 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.

#14

March 8th, 2013, 09:35 AM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation 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.

#15

March 8th, 2013, 10:19 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,380

Time spent in forums: 3 Months 1 Week 2 Days 7 h 54 m 19 sec
Reputation Power: 4140

Quote:

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)

Developer Shed Advertisers and Affiliates