Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

With this query:

SELECT SUM(qty) AS sumQty,
       cnetprodid
FROM   quoteitem
WHERE  ordered = 1
       AND sageSOPOrderReturnLineID IS NOT NULL
       AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
       AND cnetprodid LIKE 'S%'
       AND price > 10
       AND created > DATEADD(MONTH, -3, GETDATE())
GROUP  BY cnetprodid
ORDER  BY SUM(qty) DESC 

I get one row back per product, the top row has 294 as sumQty and 'S10381902' as the cnetprodid

Enter this query:

SELECT SUM(qty) AS sumQty,
       p.ProdID,
       p.CatID
FROM   quoteitem
       INNER JOIN cds_prod AS p
         ON p.ProdID COLLATE Latin1_General_CI_AS = cnetprodid
       INNER JOIN cds_atr AS a
         ON a.ProdID COLLATE Latin1_General_CI_AS = p.ProdID
       INNER JOIN lan_cat_sections AS c
         ON c.attID = a.atrID
             OR ( LEN(ISNULL(c.attID, '')) = 0
                  AND c.cat_id LIKE p.CatID + '%' )
                AND ( ( c.[include] = 0
                        AND a.valID NOT IN (SELECT Value
                                            FROM   Split(c.valID, ',')) )
                       OR ( c.[include] = 1
                            AND a.valID IN (SELECT Value
                                            FROM   Split(c.valID, ',')) ) )
WHERE  ordered = 1
       AND sageSOPOrderReturnLineID IS NOT NULL
       AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
       AND cnetprodid LIKE 'S%'
       AND price > 10
       AND created > DATEADD(MONTH, -3, GETDATE())
GROUP  BY p.ProdID,
          p.CatID,
          a.prodID
HAVING SUM(qty) > 10
ORDER  BY SUM(qty) DESC 

I get one row per product back but now the row with 'S10381902' as the ProdID column has a figure of 8820 for sumQty.

All I want is a number for how many products have been sold in the last quarter, not interested in products that have sold less than 10 units, hence the HAVING part.

The COLLATE is there because the tables come from different databases; one is in SQL_Latin1_General_CP1_CI_AS and another database is in Latin1_General_CI_AS.

Fiddle: http://sqlfiddle.com/#!3/ce1ed/4

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

The likely problem is that, by joining to those additional tables, rows in quoteitem become represented more than once in the intermediate result. That is, you have a one-to-many join at some point. SQL Server then obediently sums up those rows multiple times. It's a mistake that's very easy to make.

The simple workaround is to perform your aggregate calculations before doing any one-to-many joins. It might look something like this.

SELECT q.sumQty,
       p.ProdID,
       p.CatID
FROM (
        SELECT ProdID, SUM(qty) AS sumQty, ...other columns...
        FROM quoteitem
        GROUP BY ProdID
        HAVING SUM(qty) > 10
    ) q
       INNER JOIN cds_prod AS p
         ON p.ProdID COLLATE Latin1_General_CI_AS = cnetprodid
       INNER JOIN cds_atr AS a
         ON a.ProdID COLLATE Latin1_General_CI_AS = p.ProdID
       INNER JOIN lan_cat_sections AS c
         ON c.attID = a.atrID
             OR ( LEN(ISNULL(c.attID, '')) = 0
                  AND c.cat_id LIKE p.CatID + '%' )
                AND ( ( c.[include] = 0
                        AND a.valID NOT IN (SELECT Value
                                            FROM   Split(c.valID, ',')) )
                       OR ( c.[include] = 1
                            AND a.valID IN (SELECT Value
                                            FROM   Split(c.valID, ',')) ) )
WHERE  ordered = 1
       AND sageSOPOrderReturnLineID IS NOT NULL
       AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
       AND cnetprodid LIKE 'S%'
       AND price > 10
       AND created > DATEADD(MONTH, -3, GETDATE())
GROUP  BY p.ProdID,
          p.CatID,
          a.prodID
ORDER  BY q.sumQty DESC 
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.