Home Forums SQL Server 7,2000 General Counting regardless if match exists or not RE: Counting regardless if match exists or not

  • Hello,

    It looks as if it should be a fairly easy solution, but without some table structures and some sample data it is difficult to help! See http://www.sqlservercentral.com/articles/Best+Practices/61537/ on how to post ddls and sample data.

    That said, it looks as if you are over-complicating the sum case part, you should only need to do one sum case per column in you output, in fact because you are applying no other criteria than manufacturer and product, you probably only need a count(*) function, but I don't think this is the whole story as you have specified "EXCHANGE UPGRADE BUYER REMORSE" in the output — however, there are seemingly only 3 calculated columns in the output... what criteria are these other columns being calculated on?

    For just the straight count you will need something like (note this is TOTALLY untested and probably won't run as-is! need sample data to test)

    SELECT

    Product.ProductName,

    Returns.Manu_Name, -- Why manufacturer name in returns table - not normalised?

    COALESCE(COUNT(DISTINCT Returns.Returns_ID), 0) AS Exchange

    FROM Product

    LEFT JOIN Returns ON Product.Product_ID = Returns.Product_ID

    GROUP BY Product.ProductName, Returns.Manu_Name

    [/CODE]

    Apologies if this response is unclear, please provide create table code, and sample data in readily consumable format (see link above) and someone will be be able to help!

    Hope this gets you a bit further along.

    Allister

    /// edit added disclaimer on untested code