CODEWITHSUNDEEP
mysqlsql
user3014966
user3014966

Reputation: 263

Mysql using count in query

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 .

Upvotes: 1

Views: 75

Answers (4)

Filipe Silva
Filipe Silva

Reputation: 21657

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.

Upvotes: 1

AeroX
AeroX

Reputation: 3443

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

Upvotes: 1

Mihai
Mihai

Reputation: 26784

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

Upvotes: 1

Andrew
Andrew

Reputation: 8703

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.

Upvotes: 1

Related Questions