# Thread: Counting issue in table

1. Registered User
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. Window Washer
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```

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

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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
•