Distinct keyword

[Gavroche00]

Hi, I am very new at this, and hope someone can help.

I have a query that gives me the price of the taxi fare depending on the route. It also logs the vehicle number. Unfortunately everything works except the fact that if a taxi did 10 fares, under the count function for the total, it counts this particular behicle number 10 times. I.E. my vehicle number count is equal to my fare count. I read in the bible about the word distinct, but have no clue as to how to incorporate it in my query.

Is it possible in the same query to get one column to be counted on distinct values while the one next is all values? And if yes, how do I do it?

Thank you for all help.

 

[lespaul]

why don't you post some sample data from your tables, the query you have now that doesn't work, and what result you want from the query.

Leslie

 

[afryer]

Hi,

Try:

SELECT DISTINCT COUNT (<field_name>) FROM <table_name>

Andrew

 

[Gavroche00]

The table looks like this:

Taxi# Fare
01 $10.00
02 $8.00
03 $4.00
01 $6.00
02 $5.00

My query is an aggregate query with counts for both colums, but it returns 5 for each while I would like it to return 3 in the Taxi# column and 5 in the fare column. Hope that makes it more clear.

 

[r937]

the stanadrd way of doing this is:

select count(distinct Taxi) as Taxis
     , count(*) as Fares
  from thetable

problem is, some versions of Access do not support COUNT DISTINCT

therefore, create the following query:

select Taxi
     , count(*) as Fares
  from thetable
group
    by Taxi

save that query as TaxiFares and then run this query:

select count(*) as Taxis
     , sum(Fares) as SumFares
  from TaxiFares

rudy

http://r937.com/

SQL Consulting