CODEWITHSUNDEEP
mysql
Gooseman
Gooseman

Reputation:

How do you COUNT outside of the WHERE statement in MYSQL

I have a table - something like:

A|B
1|1
1|2
1|3
2|1
2|3
2|4
2|5
3|5

My query returns the distinct values in A if they coincide with a value in B of 1 or 2 - so

A
1
2

I am trying to also return the original count of the 1's and 2's in column A - to get something like

A|Count
1|3
2|4

Is there a simple way to get this count please? However, the COUNT (A) returns the number of A's coinciding with the initial WHERE statement:

A|Count
1|2
2|1

Thanks!

Upvotes: 1

Views: 209

Answers (4)

araqnid
araqnid

Reputation: 133492

Another way:

SELECT a, (SELECT count(*) FROM t t2 WHERE t2.a = t.a) a_count
FROM t
WHERE b IN (1,2)
GROUP BY a

Upvotes: 1

Tomalak
Tomalak

Reputation: 338228

Similar to Bill Karwin's answer:

SELECT
  A,
  Counted.CountOfB
FROM
  MyTable
  INNER JOIN (
    SELECT A, COUNT(B) AS CountOfB
    FROM   MyTable
    GROUP BY  A
  ) Counted ON Counted.A = MyTable.A
WHERE
  {your filter for MyTable}

Upvotes: 0

CodingWithSpike
CodingWithSpike

Reputation: 43718

My SQL might be a bit rusty, but I think you can do:

SELECT A, count(*) AS Count FROM MyTable WHERE B IN (1, 2) GROUP BY A;

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562368

SELECT t1.A, COUNT(DISTINCT t1.B)
FROM MyTable t1 JOIN MyTable t2 ON (t1.A = t2.A)
WHERE t2.A = t2.B
GROUP BY t1.A;

Upvotes: 0

Related Questions