CODEWITHSUNDEEP
sqlsqlite
Methonis
Methonis

Reputation: 49

Multiple COUNTs in query returning incorrect results

I'm using SQLite, and I'm pretty new to SQL in general. I'm having a problem with a query whereby my new COUNT statements are returning the same numbers when they shouldn't be. When I run the two sub queries separately I'm getting the correct results, so I know I'm just stuffing up the mashing them together part. Any suggestions on how to make this less ugly is much appreciated as well!

I'm using the following very ugly select statement:

SELECT BATCH_ID, PROGRAM_ID,
    COUNT((SELECT ID FROM PARTS
             WHERE DROPSHIP_ONLY = 'No'
             AND OBSOLETE = 'No'
             AND DISCONTINUED = 'No'))
             AS 'Active Parts',
    COUNT((SELECT ID FROM PARTS
             WHERE DROPSHIP_ONLY = 'No'
             AND OBSOLETE = 'No'
             AND DISCONTINUED = 'No'
             AND ON_HAND > 0
             AND ON_HAND > SAMPLE_LEVEL))
             AS 'Instock Parts'
             FROM PARTS
             GROUP BY BATCH_ID, PROGRAM_ID;

It returns this:

BATCH_ID  PROGRAM_ID Active Parts Instock Parts
201311    Vendor1    1390         1390
201311    Vendor2    1433         1433
201311    Vendor3    213          213
201311    Vendor4    3595         3595
201311    Vendor5    98           98
201311    Vendor6    3            3

Expected result for Active Parts is:

201311   Vendor1    1144
201311   Vendor2    1380
201311   Vendor3    100
201311   Vendor4    2276
201311   Vendor5    98
201311   Vendor6    3

Expected result for Instock Parts is:

201311   Vendor1    15
201311   Vendor2    1272
201311   Vendor3    45
201311   Vendor4    1767
201311   Vendor5    97
201311   Vendor6    3

Thanks for any help or suggestions!

Upvotes: 1

Views: 64

Answers (1)

John Woo
John Woo

Reputation: 263803

Try it using CASE()

SELECT  BATCH_ID,
        PROGRAM_ID,
        SUM(CASE WHEN DROPSHIP_ONLY = 'No' 
                        AND OBSOLETE = 'No' 
                        AND DISCONTINUED = 'No' 
                THEN 1 END) AS 'Active Parts',
        SUM(CASE WHEN DROPSHIP_ONLY = 'No' 
                        AND OBSOLETE = 'No' 
                        AND DISCONTINUED = 'No'
                        AND ON_HAND > 0 
                        AND ON_HAND > SAMPLE_LEVEL 
                THEN 1 END) AS 'Instock Parts'
FROM    PARTS
WHERE   DROPSHIP_ONLY = 'No' 
        AND OBSOLETE = 'No'
        AND 
        (
            (DISCONTINUED = 'No') 
            OR
            (DISCONTINUED = 'No' 
            AND ON_HAND > 0 
            AND ON_HAND > SAMPLE_LEVEL)
        )
GROUP   BY BATCH_ID, PROGRAM_ID

Upvotes: 1

Related Questions