CODEWITHSUNDEEP
mysqlsql
user1811989
user1811989

Reputation:

Query to solve in mysql

My table have data like this

id    from
1    1|Chinmoy Panda|chinmoy|mfsi_chinmoyp
1    532|Narendra Mallik|narendram
1    595|Bhagirathi Panda|bhagirathi

2    1|Chinmoy Panda|chinmoy|mfsi_chinmoyp
2    532|Narendra Mallik|narendram
2    595|Bhagirathi Panda|bhagirathi
2    13|Hemendra Singh|hemendras

3    1|Chinmoy Panda|chinmoy|mfsi_chinmoyp
3    595|Bhagirathi Panda|bhagirathi
3    13|Hemendra Singh|hemendras

4    1|Chinmoy Panda|chinmoy|mfsi_chinmoyp 
4    595|Bhagirathi Panda|bhagirathi

5    595|Bhagirathi Panda|bhagirathi

i am trying to this

  1. Ignore the 1st row of every id
  2. Count w.r.t. from
  3. Ignore if the id has one row.

Means 

Count     from
4         595|Bhagirathi Panda|bhagirathi
2         532|Narendra Mallik|narendram
2         13|Hemendra Singh|hemendras

In 1,2,3,4 id 1st row contains chinmay panda.So i ignore that one

Bhagirathi Panda occured 5 times but id 5 having only one row so count is 4. similarly for others

i tried but unable to find the result

please help me to write the query

(i didn't get what should the title so i write this one. )

thanks in advance.

Upvotes: 2

Views: 122

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You want a query something like this:

select count(*), from
from t
where left(from, 2) <> '1|' and
      t.id in (select id from t group by id having COUNT(*) > 1)
group by from

However, because the column names are poorly named (using SQL reserved words), you need to properly quote them.

Also, I'm assuming that by "first" you mean the ones that start with '1|'.

Upvotes: 3

Sir Rufo
Sir Rufo

Reputation: 19106

this query will do

SELECT 
  COUNT(*) `count`,
  `from`
FROM (
  SELECT 
    `from`,
    IF( COALESCE( @id, 0 ) = (@id := id) , @curRow := @curRow + 1, @curRow := 1 ) curRow
  FROM
    Table1 ) tmp
WHERE curRow > 1
GROUP BY `from`
ORDER BY `count` desc

SQL Fiddle DEMO

Upvotes: 0

Related Questions