Easy SQL question 2

[eladi]

Hi all,

This must be easy to achieve. I searched the forum already, but didn't find a post that really helped me out.

I have a db which pictures from my travels. I have a page where I say something short about a continent and then want to desplay from which countries there are pictures available and how many. The first part is easy:

<cfquery name=&quot;africa&quot; datasource=&quot;travels&quot;>
select distinct country from pictureDB where continent = 'Africa' order by country
</cfquery>

<cfoutput query=&quot;africa&quot;>
<li class=&quot;text&quot;>#country#</li>
</cfoutput>

But how do I how do I display how many entries there are of a certain country. It must be something with count(). Can't find the correct solution though.

Adrian

 

[r937]

instead of your distinct query, try this

[tt]select country, count(*) as pics
from pictureDB
where continent = 'Africa'
group by country
order by count(*) desc[/tt]

rudy

 

[csteinhilber]

Buuuuuut... wouldn't pics then return the total number of entries, rather than the number of entries in each group (country)?? Hope it helps,
-Carl

 

[r937]

no, because the query contains a GROUP BY


try it

 

[csteinhilber]

Coolness!

That earns a star, and it wasn't even my question! Hope it helps,
-Carl

 

[eladi]

hei rudy,
excellent! works perfectly, tnx a lot! the star is yours!
adrian