How to count rows that have the same values in two columns (SQL)?

Question

I am sure there must be a relatively straightforward way to do this, but it is escaping me at the moment. Suppose I have a SQL table like this:

+-----+-----+-----+-----+-----+
|  A  |  B  |  C  |  D  |  E  |
+=====+=====+=====+=====+=====+
|  1  |  2  |  3  | foo | bar | << 1,2
+-----+-----+-----+-----+-----+
|  1  |  3  |  3  | biz | bar | << 1,3
+-----+-----+-----+-----+-----+
|  1  |  2  |  4  |  x  |  y  | << 1,2
+-----+-----+-----+-----+-----+
|  1  |  2  |  5  | foo | bar | << 1,2
+-----+-----+-----+-----+-----+
|  4  |  2  |  3  | foo | bar | << 4,2
+-----+-----+-----+-----+-----+
|  1  |  3  |  3  | foo | bar | << 1,3
+-----+-----+-----+-----+-----+

Now, I want to know how many times each combination of values for columns A and B appear, regardless of the other columns. So, in this example, I want an output something like this:

+-----+-----+-----+
|  A  |  B  |count|
+=====+=====+=====+
|  1  |  2  |  3  |
+-----+-----+-----+
|  1  |  3  |  2  |
+-----+-----+-----+
|  4  |  2  |  1  |
+-----+-----+-----+

What would be the SQL to determine that? I feel like this must not be a very uncommon thing to want to do.

Thanks!

Lukasz Lysik · Accepted Answer · 2009-10-20 20:22:31Z

78

SELECT A,B,COUNT(*)
FROM the-table
GROUP BY A,B

answered Oct 20, 2009 at 20:22

Lukasz Lysik

10.7k4 gold badges53 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

KM. · Accepted Answer · 2009-10-20 20:22:39Z

15

TRY:

SELECT
    A, B , COUNT(*)
    FROM YourTable
    GROUP BY A, B

answered Oct 20, 2009 at 20:22

KM.

104k34 gold badges183 silver badges214 bronze badges

Comments

Moduo · Accepted Answer · 2013-12-27 11:38:40Z

11

This should do it:

SELECT A, B, COUNT(*) 
FROM TableName
GROUP BY A, B;

edited Dec 27, 2013 at 11:38

Moduo

6236 silver badges18 bronze badges

answered Oct 20, 2009 at 20:23

Ken White

126k15 gold badges237 silver badges476 bronze badges

Comments

Adriaan Stander · Accepted Answer · 2009-10-20 20:24:01Z

7

SELECT A,B,COUNT(1) As COUNT_OF
FROM YourTable
GROUP BY A,B

answered Oct 20, 2009 at 20:24

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Comments

Moduo · Accepted Answer · 2013-12-27 11:33:42Z

6

SELECT A,B,COUNT(*)
FROM table
GROUP BY A,B

edited Dec 27, 2013 at 11:33

Moduo

6236 silver badges18 bronze badges

answered Oct 20, 2009 at 20:23

Radu094

28.6k16 gold badges66 silver badges80 bronze badges

Comments

pmarflee · Accepted Answer · 2009-10-20 20:23:33Z

5

SELECT A, B, COUNT(*) FROM MyTable GROUP BY A, B

answered Oct 20, 2009 at 20:23

pmarflee

3,42622 silver badges22 bronze badges

Comments

Moduo · Accepted Answer · 2013-12-27 11:38:26Z

5

This could be the answer:

SELECT a, b, COUNT(*) 
FROM <your table name here> 
GROUP BY a,b 
ORDER BY 3 DESC;

edited Dec 27, 2013 at 11:38

Moduo

6236 silver badges18 bronze badges

answered Oct 20, 2009 at 20:28

snahor

1,2111 gold badge11 silver badges16 bronze badges

4 Comments

OMG Ponies Over a year ago

Also correct, but ordering by ordinals isn't a good habit iirc.

snahor Over a year ago

I thought I'd be the first to answer T_T.

snahor Over a year ago

@rexem I suppose you say it because of readability, I can't find other reason.

OMG Ponies Over a year ago

@snahor: It's because the ordinal is associated with the position in the SELECT. If it moves, your ordering changes.

Collectives™ on Stack Overflow

How to count rows that have the same values in two columns (SQL)?

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related