COUNT(DISTINCT xxx)

• Jerome GIL

  SSC Veteran

  Points: 274

  More actions

  February 1, 2006 at 5:47 am

  #170320

  Hi,

  I try to count the number of distinct field in a column, but i fail.

  My table named "car" is:

  no marq cat

  ----------------------

  1 FORD AA

  2 FORD AC

  3 FORD AZ

  4 RENAULT RT

  5 RENAULT RY

  6 PSA PL

  7 PSA PM

  when i do: "SELECT DISTINCT marq FROM car"

  i get 3 aswers : FORD, PSA, RENAULT ...that is ok

  when i do: "SELECT COUNT(marq) FROM vehicule"

  i get 1 answer: 7 ...all the rows are counted

  when i do: "SELECT COUNT(DISTINCT marq) FROM vehicule"

  i get a parse error int the SQL Server CE analyser of my emulator.

  when i do: "SELECT COUNT(*) FROM (SELECT DISTINCT marq FROM vehicule)"

  i get the same error..

  I want to obtain 3 , because i have 3 differents trades.

  Could you help me for the right SQL statement to obtain the right result.

  Thanks,

• David A. Long

  SSCrazy Eights

  Points: 8421

  More actions

  February 2, 2006 at 1:31 am

  #618297

  When:

  SELECT DISTINCT marq FROM car

  You are really doing:

  SELECT marq FROM car GROUP BY marq

  So:

  SELECT COUNT(DISTINCT marq) AS Marqs FROM car GROUP BY marq

  Is returning the rows you expect, verify using:

  SELECT marq, COUNT(marq) AS Marqs FROM car GROUP BY marq

  HTH,

  Andy

• bdarko

  SSC Veteran

  Points: 288

  More actions

  February 2, 2006 at 1:33 am

  #618298

  Andy,

  you missed aliasing the distinct select.

  Try this:

  SELECT COUNT(*) FROM (SELECT DISTINCT marq FROM vehicule) as a

  Darko

  
  First rule of debugging:
  No Code, No Bugs

• David A. Long

  SSCrazy Eights

  Points: 8421

  More actions

  February 2, 2006 at 1:41 am

  #618302

  Darko,

  Since no definition of the vehicule table was given, I did not attempt to guess. If it is a many table to car, then:

  SELECT COUNT(DISTINCT marq) AS Marqs FROM vehicule

  Or you may try:

  SELECT marq, COUNT(marq) AS Marqs FROM vehicule GROUP BY marq

  If what you are looking for is a count of the "vehicule" rows for each Marq.

  Maybe the CE engine does not support COUNT(DISTINCT..., If so try:

  SELECT COUNT(*) AS Marqs FROM vehicule GROUP BY marq

  Andy

• bdarko

  SSC Veteran

  Points: 288

  More actions

  February 2, 2006 at 2:05 am

  #618307

  Andy,

  my apologies for quoting your name in my post.

  It seems that you have posted a second before I clicked Post reply so I read your name instead of Jerome's

  My post was intendet for Jerome.

  have a nice day

  Darko

  
  First rule of debugging:
  No Code, No Bugs

• Jerome GIL

  SSC Veteran

  Points: 274

  More actions

  February 2, 2006 at 2:08 am

  #618310

  First thanks to answer me.

  Then vehicule is a mistake, the right table's name is "car" and i really try the queries with car.

  I try:

  SELECT marq, COUNT(marq) AS Marqs FROM car GROUP BY marq

  and i didn't get any errors but the result is:

  marq      Marqs

  Ford       3

  RENAULT 2

  PSA        2

  and what i want as result is one row and one column, i want 3 b/c there are three diferents marq.

  So then i try:

  SELECT COUNT(*) AS Marqs FROM car GROUP BY marq

  and the result is:

  Marqs

  3

  2

  2

  but not 3 for the differents marq...

  There must be a way to do it with CE!

  HELP

   

• Neil Coe

  SSC Journeyman

  Points: 87

  More actions

  February 2, 2006 at 4:04 am

  #618337

  Because you emulator will not allow Count(Distinct(marq)) then try

  select count(marq) from Car c

  where c.no IN (select max(no) from Car group by marq)

  assuming that [no] is a record key.

• Jerome GIL

  SSC Veteran

  Points: 274

  More actions

  February 2, 2006 at 6:57 am

  #618374

  Ok Neil Coe !!

  thank you very much!!!!

  SELECT COUNT(marq) FROM car WHERE car.id_car IN (SELECT MAX(id_car) FROM car GROUP BY marq)

  the result is 3

  So my problem is solved.

  See you soon,