Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Distinct, count and sort in one sql query

This is my first question on stackoverflow, welcome everybody.

I have a table:

id   fk_user
1      1
2      1
3      3
4      2
5      3

And I would like to prepare an SQL query witch returns fk_user sorted by the number of occurrences in that table. For instance:

fk_user 1 occurs 3 times, so it will be first.
fk_user 2 occurs once, so it will be last.
fk_user 3 occurs twice, so it will be the second.

Result of that query should be:

fk_user
1
3
2
like image 373
pmajcher Avatar asked Apr 12 '12 11:04

pmajcher


People also ask

Can we use distinct and count together in SQL?

Yes, you can use COUNT() and DISTINCT together to display the count of only distinct rows. SELECT COUNT(DISTINCT yourColumnName) AS anyVariableName FROM yourTableName; To understand the above syntax, let us create a table.

Can we use multiple distinct in single query?

Yes, the DISTINCT clause can be applied to any valid SELECT query. It is important to note that DISTINCT will filter out all rows that are not unique in terms of all selected columns.

How do I use select and count together in SQL?

In SQL, you can make a database query and use the COUNT function to get the number of rows for a particular group in the table. Here is the basic syntax: SELECT COUNT(column_name) FROM table_name; COUNT(column_name) will not include NULL values as part of the count.

How do I get counts of different values in the same column in SQL?

To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.


1 Answers

select fk_user from 
xxx
group by fk_user
order by count(*) desc
like image 160
Raphaël Althaus Avatar answered Sep 19 '22 11:09

Raphaël Althaus