SQL Server Performance

Looking for a way to count records only once in a resultset

Discussion in 'ALL SQL SERVER QUESTIONS' started by Mark Gulati, Aug 20, 2012.

  1. Mark Gulati New Member

    Hi, I have a situation in which I need to query the database and return the count of certain results, after counting the ID only once.

    Allow me to explain:

    Code:
    Item ID|Category|Sub-Category|Distributor
    1      |Camping |Tents      |Wal-Mart
    2      |Camping |Tents      |Target
    3      |Camping |Tents      |Wal-Mart
    3      |Camping |Tents      |Target
    4      |Camping |Tents      |Wal-Mart
    4      |Camping |Tents      |K-Mart 
    Here are the expected counts for the above table,

    # of Distinct Item IDs : 4
    # of Items sold at Wal-Mart : 3
    # of Items sold at Target: 2
    # of Items sold at K-Mart: 1
    # of items sold at Wal-Mart and Target: 4 (As the Item ID #3 is sold at both Wal-Mart and Target, the user did not want to count 1 for Wal-Mart and count 1 for Target. Just only one count for both)


    Right now I'm using two nested queries, in the form of

    select count(*) from
    (select distinct x.itemid from
    (select distinct i.item_id as itemid, i.category, i.subcategory, d.dist_name, d.dist_id
    from item i join distributor d on i.itemid = d.item_id)
    as x)
    as xy

    This is very cumbersome, I don't know if its a good query and I'd like an easier way of getting the counts since I'm only using the query to find a way to do this in Cognos.
  2. FrankKalis Moderator

    Welcome to the forum!
    I'm not sure this can be addressed all in one query. It could be done like this, but uses possibly more queries that you would want to have:
    Code:
    SELECT 'Distinct Item IDs', COUNT(DISTINCT itemid) FROM <table>
    UNION
    SELECT 'Items sold at'  + distributor, COUNT(*) FROM <table> GROUP BY distributor
    UNION
    SELECT 'items sold at Wal-Mart and Target', COUNT(DISTINCT itemID) FROM <table>
    WHERE distributor IN ('Wal-Mart', 'Target')
    
    Also, don't expect great performance out of that, Especially when the underlying tables get bigger. To be honest, this really looks more like a case for some data-mining tool on a OLAP database and not something that you would want to run against a production OLTP database.

Share This Page