Selecting top 5 most occurrences 1

[vpekulas]

Hello all,

I have a small problem I need help with
I have a table called tbl_down, this table has 2 fields
ID, fldCOUNTRY

Now I want to select unique countries and how many times they occur.
I'm using this query:

SELECT fldCOUNTRY FROM TBL_DOWN GROUP BY fldCOUNTRY

How can I get the sum of occurrences for each country ?

"Taxes are the fees we pay for civilized society" G.W.

 

[r937]

[tt]SELECT fldCOUNTRY
, count(*) as howmany
FROM TBL_DOWN
GROUP
BY fldCOUNTRY[/tt]


rudy

 

[vpekulas]

Thanks r937, works like a charm

"Taxes are the fees we pay for civilized society" G.W.

 

[vpekulas]

One more Q:

How can I sort the results by 'howmany' values in DESC order ?

"Taxes are the fees we pay for civilized society" G.W.

 

[r937]

add ORDER BY 2 DESC (for the 2nd column in the result set) or ORDER BY count(*) DESC or in some databases you can also say ORDER BY howmany DESC (using the alias name)

rudy

 

[vpekulas]

Once again, thank you !

"Taxes are the fees we pay for civilized society" G.W.

 

[JarlH]

Order by column number isn't ANSI compliant anymore. (Was in SQL-92, but not in SQL-99.)

Order by expression, like count(*) is SQL-99, but not SQL-92.

The only SQL-92 *and* SQL-99 compliant solution is to use an alias, like Rudy specified in previous posting:

SELECT fldCOUNTRY, COUNT(*) AS HowMany
FROM tbl_down
GROUP BY fldCOUNTRY
ORDER BY HowMany DESC