How to count rows in (SQL)?

Question

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?

Try something like this: stackoverflow.com/questions/22391452/… — Aura
– Aura, Commented May 15, 2018 at 17:56

Yogesh Sharma · Accepted Answer · 2018-05-15 17:56:42Z

1

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.

answered May 15, 2018 at 17:56

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

1 Comment

Hans_Cusco Over a year ago

Sorry, it was an example and thanks it works for me. I' m using 3 tables with that query, thats why i forgot to write the relation in the example

Shadab Kazi · Accepted Answer · 2018-05-15 17:54:38Z

0

Select count(A.I) from tablename;

answered May 15, 2018 at 17:54

Shadab Kazi

729 bronze badges

Comments

Rohit.007 · Accepted Answer · 2018-05-15 17:58:49Z

0

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

answered May 15, 2018 at 17:58

Rohit.007

3,5123 gold badges23 silver badges33 bronze badges

Comments

Error_2646 · Accepted Answer · 2018-05-15 18:00:13Z

0

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.

edited May 15, 2018 at 18:00

answered May 15, 2018 at 17:54

Error_2646

3,8991 gold badge17 silver badges26 bronze badges

2 Comments

Aura Over a year ago

He/she is using mysql not SQL Server.

Error_2646 Over a year ago

@Aureate My mistake. I thought that all major DBMS supported this type of function.

Aura · Accepted Answer · 2018-05-15 18:11:13Z

0

 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

edited May 15, 2018 at 18:11

Aura

1,3032 gold badges18 silver badges31 bronze badges

answered May 15, 2018 at 17:57

Mathieu Larouche

1311 silver badge5 bronze badges

1 Comment

Robert Columbia Over a year ago

While this code may answer the question, it is better to explain how to solve the problem and provide the code as an example or reference. Code-only answers can be confusing and lack context.

Collectives™ on Stack Overflow

How to count rows in (SQL)?

5 Answers 5

1 Comment

Comments

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related