CODEWITHSUNDEEP
mysqlsql
Hans_Cusco
Hans_Cusco

Reputation: 165

How to count rows in (SQL)?

I have two tables A, B and my query show it:(join A and B)

select A.i,A.j,B.x,B.y,B.z from A inner join B on A.id=B.id_B order by A.i,A.j

.

+-----+-----+-----+-----+-----+
| A.i | A.j | B.x | B.y | B.z |
+=====+=====+=====+=====+=====+
|  bk | bg  |  1  | inf1| bar | 
+-----+-----+-----+-----+-----+
|  bk | bg  |  2  | inf2| bar | 
+-----+-----+-----+-----+-----+
|  bk | bg  |  3  | inf3|  y  | 
+-----+-----+-----+-----+-----+
|  ro | fn  |  5  | enf1| bar | 
+-----+-----+-----+-----+-----+
|  ro | fn  |  3  | enf2| bar | 
+-----+-----+-----+-----+-----+
|  st | st  |  3  | onf1| bar | 
+-----+-----+-----+-----+-----+

Now I want to know is how many times

A.i

is repeated, like that:

    +-----+-----+-----+-----+-----+
    | A.i | A.j | B.x | B.y | B.z |RepeatColumn
    +=====+=====+=====+=====+=====+---
    |  bk | bg  |  1  | inf1| bar | 3 |(bk is repeated 3 times)
    +-----+-----+-----+-----+-----+---
    |  bk | bg  |  2  | inf2| bar | 3 |(bk is repeated 3 times)
    +-----+-----+-----+-----+-----+---
    |  bk | bg  |  3  | inf3|  y  | 3 |(bk is repeated 3 times)
    +-----+-----+-----+-----+-----+---
    |  ro | fn  |  5  | enf1| bar | 2 |(ro is repeated 2 times)
    +-----+-----+-----+-----+-----+---
    |  ro | fn  |  3  | enf2| bar | 2 |(ro is repeated 2 times)
    +-----+-----+-----+-----+-----+---
    |  st | st  |  3  | onf1| bar | 1 |(st is repeated once)
    +-----+-----+-----+-----+-----+---

How can i do that query, anyone have any idea?

Upvotes: 1

Views: 70

Answers (5)

Mathieu Larouche
Mathieu Larouche

Reputation: 131

 select A.i,
 A.j, 
 B.x, 
 B.y,
 B.z, 
 count(A.i) as repeated_column 
 from A inner join B
 order by A.i, A.j

Upvotes: 0

Error_2646
Error_2646

Reputation: 3781

edit: Unfortunately (for you) it looks like MySQL doesn't support simple window functions.

Like this:

COUNT(1) OVER
  ( PARTITION BY A.i
  ) AS cnt

It depends on whether your use case is more complicated than what you've shown if it's worth cobbling a group_concat workaround, or simply using a subquery to get the counts.

Upvotes: 0

Rohit.007
Rohit.007

Reputation: 3502

Try using subquery

SELECT 
A.i,
A.j,
B.x,
B.y,
B.z,
(SELECT COUNT(1) FROM A as WHERE i = A.i) AS 'RepeatColumn'
FROM A 
INNER JOIN B 
ORDER BY A.i,A.j

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need subquery:

select A.i,A.j,
       B.x,B.y,B.z,
       (select count(*) from b where b.id = a.id) RepeatColumn
from A 
inner join B 
order by A.i,A.j;

However, i didn't find any relation between tables (A, B) in o/p. So, i just used on fly.

Upvotes: 1

Shadab Kazi
Shadab Kazi

Reputation: 72

Select count(A.I) from tablename;

Upvotes: 0

Related Questions