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

    Unanswered: 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 advance

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...really simple now...just cut and paste in to QA

    Code:
    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
    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.

Posting Permissions

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