Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: Counting issue in table

    I have a table with the following data (sample)

    Type Year Price
    ---- ---- -----
    A 00 100.00
    A 00 200.00
    A 01 105.00
    A 01 105.00-
    B 00 100.00
    B 00 200.00
    B 01 105.00
    B 02 00.00


    I need to establish a Type Count. The business rule to do the count is -

    For a particular type, take a single year and add up the price. If the price is greater than zero then count = 1 else count = 0. After completing the counts for all the years for a particular type, add up the counts.

    Based on the business rule for the above sample data I should have the following count.

    Count -
    Type A = 1 (For Year 00, price = 300.00 so count = 1 and for year 01, price = .00 (105 + 105.00 - ) and so count = 0. add counts to get a total of 1)

    Type B = 2 (For Year 00, price = 300.00 so count = 1 and for year 01, price = 105 and so count = 1 and for year 02 price = 00 so count = 0. add counts to get a total of 2)

    How do I implement this count using a query. I am free to add any indicator columns that may be required to perform the counting.

    Let me know.

    Thanks

    Vivek

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Type char(1), [Year] char(2), Price money)
    GO
    
    INSERT INTO myTable99(Type, [Year], Price)
    SELECT 'A', '00', 100.00  UNION ALL
    SELECT 'A', '00', 200.00  UNION ALL
    SELECT 'A', '01', 105.00  UNION ALL
    SELECT 'A', '01', -105.00 UNION ALL
    SELECT 'B', '00', 100.00  UNION ALL
    SELECT 'B', '00', 200.00  UNION ALL
    SELECT 'B', '01', 105.00  UNION ALL
    SELECT 'B', '02', 00.00
    GO
    
    SELECT    Type
    	, [Year]
    	, SUM(Price) AS SUM_Price
    	, CASE WHEN SUM(Price) > 0 THEN 1 ELSE 0 END AS COUNT_Price
      FROM myTable99
    GROUP BY Type, [Year]
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Posts
    176
    Thanks Brett. That helps.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can I play too? I added a snippet that I think answers the original question
    Code:
    CREATE TABLE myTable99(Type char(1), [Year] char(2), Price money)
    GO
    
    INSERT INTO myTable99(Type, [Year], Price)
    SELECT 'A', '00', 100.00  UNION ALL
    SELECT 'A', '00', 200.00  UNION ALL
    SELECT 'A', '01', 105.00  UNION ALL
    SELECT 'A', '01', -105.00 UNION ALL
    SELECT 'B', '00', 100.00  UNION ALL
    SELECT 'B', '00', 200.00  UNION ALL
    SELECT 'B', '01', 105.00  UNION ALL
    SELECT 'B', '02', 00.00
    GO
    
    SELECT    Type
    	, [Year]
    	, SUM(Price) AS SUM_Price
    	, CASE WHEN SUM(Price) > 0 THEN 1 ELSE 0 END AS COUNT_Price
      FROM myTable99
    GROUP BY Type, [Year]
    
    SELECT type, Sum(price_count)
       FROM (SELECT type, [year]
    ,     CASE WHEN 0 < Sum(price) THEN 1 ELSE 0 END AS price_count
          FROM myTable99
          GROUP BY type, [year]) AS a
       GROUP BY type
    
    GO
    
    DROP TABLE myTable99
    GO
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •