CODEWITHSUNDEEP
mysqldatabasegroup-by
Abhishek Jain
Abhishek Jain

Reputation: 888

MySQL: Group By multiple columns not giving exact results

I have a table that contains 5 columns namely:

before_1, before_2, before_3, rule, name

where before_1,before_2, and before_3 are the three words before the name/word in a document.

What I wanted to find was:

Which are the two words that occur together before a name. I don't want just the top words, but all the words sorted by the number of occurrences.

I tried the following few queries but that didn't work for me.

select count(before_2),count(before_3),name from data_with_before_words group by name;

I got the same count for both columns, which is not what I was expecting.

Example data:

First 5 rows:

before_1,before_2,before_2,rule,name

a,league,of,Persona,Amell
the,assasin,of,Persona,Amell
the,league,of,Persona,Amell
a,assasin,of,Persona,Amell
a,league,of,Persona,Amell

Expected Output:

league,of,3,Amell
assasin,of,2,Amell

Any help would be appreciated.

Upvotes: 0

Views: 53

Answers (3)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

To get the expected output you can use following query

select before_2,before_3,name,count(*)
from data_with_before_words 
group by before_2,before_3,name
order by count(*) desc

Demo

Upvotes: 1

Ronny
Ronny

Reputation: 23

Try this out

SELECT before_2,before_3,name FROM data_with_before_words GROUP BY before_2,before_3,name Having count(*)>=1

Upvotes: 0

Nans
Nans

Reputation: 779

Try this Query:

select count(res1.comWords) as occurrences, res1.name from (select concat(before_1,"-", before_2) as comWords, name from data_with_before_words) res1 group by res1.name order by occurrences desc;

Upvotes: 1

Related Questions