CODEWITHSUNDEEP
mysql
Shijilal
Shijilal

Reputation: 2169

Getting total count of distinct values

I have a mysql table with 3 fields Brand,Model and BenchMark Score.

Notes regarding how the data will be in this table.

1.A brand can have different models. 2.Different Brands can have models will same name 3.There can be different Benchmark score of the a particular brand-model combination.

So the table data will look something like this

Brand ModelName Benchmark

B1    M1         1000
B1    M1         2000
B1    M2         3000
B1    M2         2000
B2    M1         3000
B2    M3         4000

Now what i need is to find is the number of time a particular benchmark score is reported.

Here we should count only those benchmark score which is the maximum for a model-brand combination and not all those benchmark scores.

The result should be like this

Score Times
3000  2
2000  1
4000  1

So what should be the mysql statement if i need this result..please help

Upvotes: 2

Views: 753

Answers (2)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

SELECT * FROM
(SELECT MX,COUNT(1) SC FROM
(SELECT MAX(MaxScores.MaxScore) MX,ScoreCounts.ScoreCount FROM
(
    SELECT MAX(BenchMark) MaxScore,Brand,ModelName
    FROM benchmarks GROUP BY Brand,ModelName
) MaxScores
INNER JOIN
(
    SELECT COUNT(1) ScoreCount,Brand,ModelName
    FROM benchmarks GROUP BY Brand,ModelName
) ScoreCounts
USING (Brand,ModelName)
GROUP BY Brand,ModelName
) A GROUP BY MX) AA ORDER BY SC DESC,MX;

This query produced exactly what you are looking for and displays the result in the exact order your question has it. I tried it out with your sample data from the question. Here is what I got:

mysql> use test
Database changed
mysql> drop table if exists benchmarks;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE benchmarks (Brand CHAR(2),ModelName CHAR(2),BenchMark INT,key (Brand,ModelName));
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO benchmarks VALUES
    -> ('B1','M1',1000),
    -> ('B1','M1',2000),
    -> ('B1','M2',3000),
    -> ('B1','M2',2000),
    -> ('B2','M1',3000),
    -> ('B2','M3',4000);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE benchmarks\G
*************************** 1. row ***************************
       Table: benchmarks
Create Table: CREATE TABLE `benchmarks` (
  `Brand` char(2) DEFAULT NULL,
  `ModelName` char(2) DEFAULT NULL,
  `BenchMark` int(11) DEFAULT NULL,
  KEY `Brand` (`Brand`,`ModelName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM benchmarks;
+-------+-----------+-----------+
| Brand | ModelName | BenchMark |
+-------+-----------+-----------+
| B1    | M1        |      1000 |
| B1    | M1        |      2000 |
| B1    | M2        |      3000 |
| B1    | M2        |      2000 |
| B2    | M1        |      3000 |
| B2    | M3        |      4000 |
+-------+-----------+-----------+
6 rows in set (0.01 sec)

mysql> SELECT * FROM
    -> (SELECT MX,COUNT(1) SC FROM
    -> (SELECT MAX(MaxScores.MaxScore) MX,ScoreCounts.ScoreCount FROM
    -> (
    ->     SELECT MAX(BenchMark) MaxScore,Brand,ModelName
    ->     FROM benchmarks GROUP BY Brand,ModelName
    -> ) MaxScores
    -> INNER JOIN
    -> (
    ->     SELECT COUNT(1) ScoreCount,Brand,ModelName
    ->     FROM benchmarks GROUP BY Brand,ModelName
    -> ) ScoreCounts
    -> USING (Brand,ModelName)
    -> GROUP BY Brand,ModelName
    -> ) A GROUP BY MX) AA ORDER BY SC DESC,MX;
+------+----+
| MX   | SC |
+------+----+
| 3000 |  2 |
| 2000 |  1 |
| 4000 |  1 |
+------+----+
3 rows in set (0.01 sec)

Give it a Try !!!

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425371

SELECT  b.model, b.brand, bd.score, COUNT(*)
FROM    (
        SELECT  model, brand, MAX(score) AS mscore
        FROM    benchmark
        GROUP BY
                model, brand
        ) bd
JOIN    benchmark b
ON      b.model = bd.model
        AND b.brand = bd.brand
        AND b.score = bd.mscore
GROUP BY
        b.model, b.brand

Create an index on (model, brand, score) for this to work fast.

Update:

SELECT  score, COUNT(*) AS cnt
FROM    benchmark
GROUP BY
        score
ORDER BY
        cnt DESC

Upvotes: 0

Related Questions