Mysql using count in query

Question

Say that I have two tables T and T1

T

id   p   o 
 1    47  1
 2    48  2
 3    49  25

T1

     id   p   o 
     1    47  1
     2    42  2
     3    47  25

I am looking to insert rows from T1 into T if count(T1.p)>1

T

   id   p   o 
     1    47  1
     2    48  2
     3    49  25
     1    47  1
     3    47  25

I tried the following query but it didn't work

insert into T(id , p,o)(SELECT T1.id , T1.p1,T1.l FROM T1 
                        where SELECT count(*) FROM t1 
                        GROUP BY t1.p 
                        HAVING COUNT(*)>1)

For more details . Any help will be appreciated .

remove the () around the select

Mad Dog Tannen
– Mad Dog Tannen

2013-12-11 14:39:05 +00:00
Commented Dec 11, 2013 at 14:39 — Mad Dog Tannen
– Mad Dog Tannen, Commented Dec 11, 2013 at 14:39

Filipe Silva · Accepted Answer · 2013-12-11 15:22:19Z

1

To get those values into T you will have to find out who they are in T1 and JOIN them with T1 again, to get the right number of rows:

INSERT INTO T (id, p, o)
SELECT TT.*
FROM T1 TT
INNER JOIN (
  SELECT id, p1, l
  FROM T1
  GROUP BY p1
  HAVING COUNT(*) > 1
  ) a ON a.p1 = TT.p1;

sqlfiddle demo

How this works:

SELECT id, p1, l
FROM T1
GROUP BY p1
HAVING COUNT(*) > 1

Returns the p1 that appears more than once in the table. This returns p1 = 47. GROUP BY p1 HAVING COUNT(*) > 1 makes sure that for each p1, we only want the results that appear more than once.

Then, we do an inner JOIN with T1 again, to get all rows that have P1 = 47:

ID  P1  L
1   47  1
3   47  25

Then you just INSERT this result in the destination table.

edited Dec 11, 2013 at 15:22

answered Dec 11, 2013 at 14:48

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

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

2 Comments

user3014966 Over a year ago

Could you please explain me how you successfully get the two rows (1, 47, 1)and ( 3, 47, 25)

Filipe Silva Over a year ago

@user3014966. I added a little explanation

Andrew · Accepted Answer · 2013-12-11 14:44:39Z

1

You have a couple of errors in your select.

This should get you going:

SELECT T1.id , T1.p1,T1.l 
                         FROM t1 
                        GROUP BY t1.p1 
                        HAVING COUNT(*)>1

SQL Fiddle

EDIT: Updated the SQL Fiddle to include the insert.

edited Dec 11, 2013 at 14:44

answered Dec 11, 2013 at 14:39

Andrew

8,8933 gold badges31 silver badges54 bronze badges

2 Comments

Ravindra Gullapalli Over a year ago

It is returning only 1 row.

Andrew Over a year ago

Yes, that's because your select is only returning one row. If you group by p1, only one row in the result has a count of > 1.

Mihai · Accepted Answer · 2013-12-11 14:51:24Z

1

insert into T SELECT T1.id , T1.p1,T1.l FROM T1 
GROUP BY t1.p1 
HAVING COUNT(t1.p1)>1

http://www.sqlfiddle.com/#!2/75c8e/1

Use dml on the left side

edited Dec 11, 2013 at 14:51

answered Dec 11, 2013 at 14:38

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

1 Comment

Ravindra Gullapalli Over a year ago

It is returning only 1 row.

AeroX · Accepted Answer · 2013-12-11 15:11:14Z

1

The below SQL should do what your looking for:

INSERT INTO T (id, p, o)
SELECT id, p1, l
FROM T1
WHERE p1 IN (
  SELECT p1
  FROM T1
  GROUP BY p1
  HAVING COUNT(*) > 1
);

SQL Fiddle - Final result

edited Dec 11, 2013 at 15:11

answered Dec 11, 2013 at 14:53

AeroX

3,4712 gold badges28 silver badges40 bronze badges

Collectives™ on Stack Overflow

Mysql using count in query

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related