Saturday, February 25, 2012

problem counting multiple occurrances of a pair of entries

Apologies for the previous post - I hit the wrong mouse button

I was having problems writing up this query

I had a table with columns A B C i needed to write up a query that would count all occurrances of unique A,B pair entries in the table

for eg:

A B C
red pink x
red pink y
green blue z
red pink a
green yello b
green blue c

The query should return

red pink 3
green blue 2
green yello 1

I hope that helps... any help would be appreciated

Thanks in advanceOK...really simple now...just cut and paste in to QA

USE Northwind
GO

CREATE TABLE myTable99 (A varchar(10),B varchar(10),C varchar(10))
GO

INSERT INTO myTable99 (A,B,C)
SELECT 'red', 'pink', 'x' UNION ALL
SELECT 'red', 'pink', 'y' UNION ALL
SELECT 'green','blue', 'z' UNION ALL
SELECT 'red', 'pink', 'a' UNION ALL
SELECT 'green','yello', 'b' UNION ALL
SELECT 'green','blue', 'c'

SELECT A,B,COUNT(*)
FROM myTable99
GROUP BY A,B
GO

DROP TABLE myTable99
GO

No comments:

Post a Comment