8

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 ) ?

aleroot
  • 71,077
  • 30
  • 176
  • 213

2 Answers2

10

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
Derek
  • 21,828
  • 7
  • 53
  • 61
  • 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 : http://stackoverflow.com/questions/7088107/how-to-get-a-nested-sub-query-to-recognize-parent-query-column – aleroot Sep 02 '11 at 16:19
  • 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 ? – aleroot Sep 02 '11 at 17:12
  • Nothing inherently, no - try adding indexes, etc. – Derek Sep 02 '11 at 17:15
3

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:

I tried to help you with the distinct issue.

Community
  • 1
  • 1
Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
  • Why not just use concat? I do not feel confident using mathematics operator – ajreal Sep 02 '11 at 16:47
  • 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 Sep 02 '11 at 16:47