Thanks Dennis, Your query seems really good.
Why SQL doesn't allow "select COUNT (DISTINCT column1, column2) from table"? When it allows: "select DISTINCT column1, column2 from table" and "select COUNT (DISTINCT column1) from table". Regards, Phani -----Original Message----- From: Dennis Povshedny [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 25, 2007 4:40 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] select COUNT (DISTINCT column1, column2) from table? Hi Phani! For your sample the following query will fit: select COUNT (DISTINCT year*12+month) FROM m If you take a look at EXPLAIN select COUNT (DISTINCT year*12+month) FROM m you will see that effectiveness is almost the same than in case of EXPLAIN select COUNT (DISTINCT year) FROM m and significantly better than in SELECT COUNT(*) FROM ( SELECT COUNT(*) FROM m group by year,month); If it is not a real sample and you have string data you may concatenate or something like this. Hope this helps. Regards, Dennis Xeepe Phone Solution Team http://en.xeepe.com mailto:[EMAIL PROTECTED] sip:[EMAIL PROTECTED] -----Original Message----- From: B V, Phanisekhar [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 25, 2007 2:46 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] select COUNT (DISTINCT column1, column2) from table? Hi Simon, Yeah, I thought of the query which u mentioned. But the problem is overhead is too much. I was wondering why SQL doesn't support something like: Select COUNT (DISTINCT year, month) FROM table when it supports: select COUNT (DISTINCT year) FROM table Regards, Phani No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.488 / Virus Database: 269.13.30/1029 - Release Date: 24.09.2007 19:09 ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
