CODEWITHSUNDEEP
sqlms-accesscount
quant
quant

Reputation: 4482

count and order in sql at ms-access

I have a table (called Tweets) in ms-access that looks like this

text    screenName
lala      user1
la        user2
lal       user3
lalala    user1
l         user1
a         user4

I would like to have in the end a table, that will contain the number of text per user. The column with the number of text, I would like to have a certain name for it (e.g. newColumn) and also the table to be sorted first by the number of texts (newColumn) (descending) and then by the screenName (ascending).

So far I have 

SELECT COUNT(text), screenName
FROM Tweets
GROUP BY screenName;

But I don't know how to sort and to rename the new column.

Upvotes: 1

Views: 1662

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

Like this:

select COUNT(text) as num_of_texts,
    screenName
from Tweets
group by screenName
order by COUNT(text) desc, screenName;

Upvotes: 1

Related Questions