CODEWITHSUNDEEP
mysqlmysql-workbench
yas
yas

Reputation: 519

mysql query count

I got the following mysql table

PrefixID + Prefix
001      + 110
005      + 550
005      + 550
005      + 550
005      + 550

and I want to turn it into the following table

PrefixID + Prefix + Count
001      + 110    + 1
005      + 550    + 4

Count is just the frequency of that particular prefix How can i achieved that?

Upvotes: 0

Views: 639

Answers (3)

Sashi Kant
Sashi Kant

Reputation: 13465

Try this :

Select 
PrefixID, Prefix, count(Prefix)
from table
group by Prefix

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33391

Try this:

SELECT
  PrefixID,
  MAX(Prefix) as  Prefix,
  Count(Prefix) as Count
FROM table
GROUP BY PrefixID

Upvotes: 1

spencer7593
spencer7593

Reputation: 108530

Use a GROUP BY clause and a COUNT() aggregate:

SELECT `PrefixID`
     , `Prefix`
     , COUNT(1) AS `Count`
  FROM mytable
 GROUP BY `PrefixID`, `Prefix`

From your sample data, it appears that the Prefix and PrefixID are dependent (one to one correspondence.) You may need to adjust the query, depending if you want to guarantee that either PrefixID or Prefix is unique in the resultset.

Upvotes: 2

Related Questions