Have Geek Brain

How do you use COUNT outside the WHERE clause 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 different values ​​in A if they are the same as the value in B from 1 or 2 - so

A
1
2

I am also trying to get the original counts 1 and 2 back in column A to get something like

A|Count
1|3
2|4

Is there an easy way to get this invoice please? However, COUNT (A) returns the number A that matches the initial WHERE clause:

A|Count
1|2
2|1

Thanks!

+1


a source to share


4 answers


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
+1


a source


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



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


a source 


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;
0


a source


Like Bill Carwin replies:

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}
0


a source






More articles:


All Articles