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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment