I have a table with a column called bid and I would like to list how many records there exists with each bid value.
Kind of making a TOP TEN list.
Example:
bid with value 1 has 5 entries/rows. bid with value 2 has 3 entries/rows. bid with value 3 has 8 entries/rows. etc
How do I make a query that counts and sums up each of the bid's and sort the in a DESCending order?
Thankful for any kind of help!
This should work in MySQL
select u.firstname, t.bid, count(*) as counts
from your_table t
join users u on u.bid = t.bid
where t.confirmed <> '0000-00-00 00:00:00'
group by t.bid
order by counts desc
Generally you can do
select u.firstname, t.bid, t.counts
from
(
select bid, count(*) as counts
from your_table
where confirmed <> '0000-00-00 00:00:00'
group by bid
) t
join users u on u.bid = t.bid
order by t.counts desc
How about this?
SELECT bid, count(*) as TotalHits
FROM tableName
GROUP BY bid
if you want result as per hits sorted, use
SELECT bid, count(*) as TotalHits
FROM tableName
GROUP BY bid
ORDER BY TotalHits DESC