Board index » delphi » Single query?

Single query?

For some reason I can't "remember" how to do this in a single query:

TRIPS:
driver     car       city
------------------------------
joe        red       new york
joe        blue      new york
joe        green     new york
joe        red       boston
joe        red       portland
bob        orange    boston
bob        black     portland
bob        yellow    new york

I need to the count of how many cities each driver has been to.  In this
case, Joe has been to 3 cities and bob has been to 3 cities, so the
result should be:

joe 3
bob 3

I can do it in two:

select driver, city from trips group by driver, city into cursor temp
select driver, count(*) from temp group by driver

Does it need two or am I blanking on this tonight?

 

Re:Single query?


SELECT driver, COUNT (DISTINCT city)
FROM Trips
GROUP BY driver

  Richard

Quote
"Richard Grossman" <usenetuserDELETETHISP...@techIII.com> wrote in message

news:3e585afa$1@newsgroups.borland.com...
Quote
> For some reason I can't "remember" how to do this in a single query:

> TRIPS:
> driver     car       city
> ------------------------------
> joe        red       new york
> joe        blue      new york
> joe        green     new york
> joe        red       boston
> joe        red       portland
> bob        orange    boston
> bob        black     portland
> bob        yellow    new york

> I need to the count of how many cities each driver has been to.  In this
> case, Joe has been to 3 cities and bob has been to 3 cities, so the
> result should be:

> joe 3
> bob 3

> I can do it in two:

> select driver, city from trips group by driver, city into cursor temp
> select driver, count(*) from temp group by driver

> Does it need two or am I blanking on this tonight?

Re:Single query?


Thanks.
Quote
Richard Biffl wrote:
> SELECT driver, COUNT (DISTINCT city)
> FROM Trips
> GROUP BY driver

>   Richard

> "Richard Grossman" <usenetuserDELETETHISP...@techIII.com> wrote in message
> news:3e585afa$1@newsgroups.borland.com...

>>For some reason I can't "remember" how to do this in a single query:

>>TRIPS:
>>driver     car       city
>>------------------------------
>>joe        red       new york
>>joe        blue      new york
>>joe        green     new york
>>joe        red       boston
>>joe        red       portland
>>bob        orange    boston
>>bob        black     portland
>>bob        yellow    new york

>>I need to the count of how many cities each driver has been to.  In this
>>case, Joe has been to 3 cities and bob has been to 3 cities, so the
>>result should be:

>>joe 3
>>bob 3

>>I can do it in two:

>>select driver, city from trips group by driver, city into cursor temp
>>select driver, count(*) from temp group by driver

>>Does it need two or am I blanking on this tonight?

Other Threads