Need to query distinct combination of two fields,

问题:

What I need is a query on a table that would return distinct combinations of columns A and B, along with the count of how many times each combination occurs in the table. This would all be sorted by Column A.

If the table were:

A    B  .......
1    1
1    1
1    1
1    2
2    1
2    1

The result would be:

A    B     count
1    1       3
1    2       1
2    1       2

Any help would be great.

回答1:

GROUP BY is your friend here:

select a,b,count(*) from test
group by a,b
order by a

SQLFiddle: http://sqlfiddle.com/#!9/062b0e/5

回答2:

Use GROUP BY like this

SELECT
    `A`,
    `B`,
    COUNT(*) AS `Count`
FROM
    `table`
GROUP BY
    `A`, `B`
ORDER BY
    `A`