Friday, February 17, 2012

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

VivekUSE 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|||Thanks Brett. That helps.|||Can I play too? I added a snippet that I think answers the original questionCREATE 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

No comments:

Post a Comment