CODEWITHSUNDEEP
mysqlsqlgroup-by
marburger93
marburger93

Reputation: 23

MySQL GROUP BY…exclude values in other field

i have a problem with a MySQL query for my statistic-page. I want to use a query similar to the following to retrieve all rows to "version" but i want to exclude values in other field "platform" if is not the same value.

Example values:

| platform | version | ...
----------------------
| Windows  | 1.0.1   |
| Windows  | 1.0.1   |
| Windows  | 1.0.1   |
| Windows  | 1.0.2   |
| Windows  | 1.0.3   |
| Linux    | 1.0.1   |
| Linux    | 1.0.1   |
| Linux    | 1.0.2   |
| Mac      | 1.0.1   |
| Mac      | 1.0.1   |

Query:

SELECT
    platform,
    version,
    COUNT(*) AS count
FROM
    user
GROUP BY
    version

Result:

| platform | version | count |
------------------------------
| Windows  | 1.0.1   | 7     |
| Windows  | 1.0.2   | 2     |
| Windows  | 1.0.3   | 1     |

I need the following result:

| platform | version | count |
------------------------------
| Windows  | 1.0.1   | 3     |
| Windows  | 1.0.2   | 1     |
| Windows  | 1.0.3   | 1     |
| Linux    | 1.0.1   | 2     |
| Linux    | 1.0.2   | 1     |
| Mac      | 1.0.1   | 2     |

I hope you can help me... and sorry for my english.

Upvotes: 2

Views: 716

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you just need the right group by clause:

SELECT platform, version, COUNT(*) AS count
FROM user
GROUP BY platform, version;

Your query is not actually syntactically correct SQL. The column platform is in the SELECT but it is not in the GROUP BY. Almost any database other than MySQL would correctly return an error.

Upvotes: 2

Related Questions