Count on DISTINCT of several fields work only on MySQL?

Question

I need a Query that without any changes work on these three different database server : MySQL, MSSQL, PostgreSQL . In this query i have to to calculate a column with the following expression that work correctly on MySQL :

COUNT(DISTINCT field_char,field_int,field_date) AS costumernum

The fields in the distinct are of different type :

field_char = character
field_int  = integer
field_date = datetime

The expression is inside a parent query select, so if i try to achieve the result with a sub query approach, i stumble in this situation :

SELECT t0.description,t0.depnum
(select count(*) from (
  select distinct f1, f2, f3 from salestable t1
  where t1.depnum = t0.depnum
) a) AS numitems
FROM salestable t0

I get an error with this query, how can i get the value of the parent query ?

The expression work correctly on MySQL but i get an error when i try to execute it on Sql Server or PostgreSQL (the problem is that the count function doesn't accept 3 arguments of different type on MSSQL/PostgreSQL), is there a way to achieve the same result with an expression that work in each of these database server (SQL Server, MySQL, PostgreSQL ) ?

Solution 1

A general way to do this on any platform is as follows:

select count(*) from (
  select distinct f1, f2, f3 from table
) a

Edit for new info:

What if you try joining to the distinct list (including the dept) and then doing the count? I created some test data and this seems to work. Make sure the COUNT is on one of the t1 columns - otherwise it will mistakenly return 1 instead of 0 when there are no corresponding entries in t1.

SELECT t0.description, t0.depnum, count(t1.depnum) as 'numitems'
FROM salestable t0
LEFT JOIN (select distinct f1,f2,f3,depnum from salestable) t1
  ON t0.depnum = t1.depnum
GROUP BY
  t0.description, t0.depnum

Solution 2

How about concatenating?

COUNT(DISTINCT field_char || '.' || 
               cast(field_int as varchar) || '.' || 
               cast(field_date as varchar)) AS costumernum

Warning: your concatenation operator may vary with RDBMS flavor.

Update

Apparently, the concatenation operator portability is question by itself:

• String concatenation operator in Oracle, Postgres and SQL Server

I tried to help you with the distinct issue.

Comments

[aleroot]

I've already tried with this approach, the only problem is that i have to filter the subquery with a value that i have to get from the parent query, see this post : stackoverflow.com/questions/7088107/…

[ajreal]

Why not just use concat? I do not feel confident using mathematics operator

[aleroot]

Cast work correctly but i can't use the same query for each database : Mysql, PostgreSQL, MSSQL, because the syntax for string concatenation is different between these database server ...

[aleroot]

Seem that it work, but on MySQL is extremely slow, i don't know why, in the other database server work pretty fast ... Do you know a solution ?

[Derek Kromm]

Nothing inherently, no - try adding indexes, etc.