can you do a COUNT on a DISTINCT query?

sneakyimp

i have this query:

SELECT DISTINCT p.unitid,s.name,p.type
            FROM myplan_schools s, myplan_school_program_assoc p
            WHERE s.unitid IN (110635,110644,201885) AND s.unitid=p.unitid
            ORDER BY s.name ASC
            LIMIT 0,10

it returns the records i am interested in and limits the number of records returned so my PHP page isn't enormous. I have a previous/next button thing set up which alters the LIMIT clause so users can page thru all the records.

I want to know how many records would have been returned without the LIMIT clause so i can display "Record 1-10 of X". Is it possible to combine COUNT and DISTINCT in a query?

bastien

you can, but not with a limit on the clause...i run two queries...one for the dataset to be displayed, and initially a query to find the total. Then I pass the total in a hidden form field (or you could requery)

coreyp_1

I don't know of a situation in which this could work (because of the LIMIT), but you could do a second quick, stripped down query:

SELECT DISTINCT COUNT(unitid) AS total 
  FROM myplan_schools s
  WHERE s.unitid IN (110635,110644,201885)

sneakyimp

i probably didn't explain it too well, but yes the limit is for display purposes. the point is to determine how many DISTINCT records i can find....distinct meaning that a single combination of unitid, name, and type occurs no more than once.

in other words if there are 2 different records where unitid is 1000, name is 'marco polo', and type is '3' then i only want to count that record once as it is not distinct as far as i am concerned.

i'm definitely going to have 2 queries. one for the count of ALL records and the second one with a LIMIT clause to just get me the records i currently need.

bastien

select count (distinct(unitid)) as total...

is the correct sql

sneakyimp

actually, no it's not

these records are distinct and should each contribute to count:

UNITID  NAME     TYPE
177026  FOOBAR   3
177026  FOOBAR   2
177026  FOOBAR   1

sneakyimp

i tried this and it won't run:

SELECT count( DISTINCT ( unitid, name,
TYPE ) ) AS total_colleges
FROM myplan_school_program_assoc