-2

I have two queries (which to me are identical with the exception of a temp table). The 1st query with the temp table provides accurate and expected results. The 2nd query w/o the temp table provides highly inflated counts. Why is such? They appear to be exactly the same query to me?

Can this query be constructed w/o a temp table and have accurate data returned?
Query 1

SELECT Distinct hacaNum as [HOV Number] , 
al.rstQMN,os.[STATE]
INTO  #helpertable     
FROM alpha al
LEFT OUTER JOIN foxtrot on  foxtrot.[foxtrot_ID] = al.[foxtrot_ID]
LEFT OUTER JOIN charche as ch on ch.[rstQMN] = al.[rstQMN] AND ch.[foxtrot_ID] = al.[foxtrot_ID]
LEFT OUTER JOIN zulu zl on zl.[SMI] = al.[SMI]
LEFT OUTER JOIN oscie os on os.[AI] = ch.[AI]
Where hacaNum = '88' 
 Order By hacaNum

SELECT [HOV Number],        
        Count([Tracking Number]) as Total
        ,State
From #helpertable
Group By [HOV Number],State
Order By [HOV Number]

Query 2

SELECT Distinct hacaNum as [HOV Number] , 
[Alabama] = COUNT(case when os.[STATE] = 'AL' then 1 else 0 end)
,[MS] = Count(case when os.[STATE] = 'MS' then 1 else 0 end)
FROM alpha al
LEFT OUTER JOIN foxtrot on  foxtrot.[foxtrot_ID] = al.[foxtrot_ID]
LEFT OUTER JOIN charche as ch on ch.[rstQMN] = al.[rstQMN] AND ch.[foxtrot_ID] = al.[foxtrot_ID]
LEFT OUTER JOIN zulu zl on zl.[SMI] = al.[SMI]
LEFT OUTER JOIN oscie os on os.[AI] = ch.[AI]
Where hacaNum = '88' 
Group By hacaNum
 Order By hacaNum

EDIT
For clarification - I am needing a way to get a count by state breakdown for Alabama and MS. (the other states do not matter). When I attempt to get counts like in my above syntax the counts get inflated greatly.

1

Instead of using Count

COUNT(case when os.[STATE] = 'AL' then 1 else 0 end)
COUNT(case when os.[STATE] = 'MS' then 1 else 0 end)

Try using SUM

SUM(case when os.[STATE] = 'AL' then 1 else 0 end)
SUM(case when os.[STATE] = 'MS' then 1 else 0 end)
1

Because the the queries are not the same, if you wan't to get the results without a temp table you can use a derived table:

SELECT
    a.hacaNum as [HOV Number],
    Count(a.rstQMN) as Total,
    a.State
FROM
    (
        SELECT DISTINCT
            hacaNum, 
            al.rstQMN,
            os.[STATE]
        FROM
            alpha al
            LEFT OUTER JOIN foxtrot on
                foxtrot.[foxtrot_ID] = al.[foxtrot_ID]
            LEFT OUTER JOIN charche as ch on
                ch.[rstQMN] = al.[rstQMN]
                AND ch.[foxtrot_ID] = al.[foxtrot_ID]
            LEFT OUTER JOIN zulu zl on
                zl.[SHIP_MODE_ID] = al.[SHIP_MODE_ID]
            LEFT OUTER JOIN oscie os on
                os.[ADDRESS_ID] = ch.[ADDRESS_ID]
        WHERE
            hacaNum = '88' 
    ) AS a
GROUP BY
    hacaNum,
    State
Order By
    hacaNum

UPDATE

If you only want the count for some states, then you need to update the WHERE clause conditions:

        WHERE
            hacaNum = '88' 
            AND os.STATE in ('AL', 'MS')

Note that the COUNT() aggregate function counts any non-null value when an expression is given, so with COUNT(case when os.[STATE] = 'AL' then 1 else 0 end) the result is count all rows that are not null (1 and 0 are both not null). The expression works using the SUM() aggregate function instead, but it is more efficient to discard the unwanted records before counting them, hence the suggestion to add the condition on the WHERE clause.

  • That provides an overall count - (and I did not make it clear in my OP, sorry) I need a way of getting a count for certain states such as MS and Alabam. When I add in the case statements the counts become skewed again. – BellHopByDayAmetuerCoderByNigh May 8 '17 at 23:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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