RE: [sqlite] select COUNT (DISTINCT column1, column2) from table?
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] -
FW: [sqlite] select COUNT (DISTINCT column1, column2) from table?
Seems useful Best Regards, A.Sreedhar. -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] -
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] -
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 -Original Message- From: Simon Davies [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 25, 2007 4:09 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] select COUNT (DISTINCT column1, column2) from table? On 25/09/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote: > Hi Simon, . . > Here I > need to find the number of distinct combinations of year, month not the > count for a particular year, month. > > Regards, > Phani > SQLite version 3.4.2 Enter ".help" for instructions sqlite> sqlite> create table m( mNo integer, year integer, month integer ); sqlite> sqlite> insert into m values (1, 2006, 11 ); sqlite> insert into m values (2, 2007, 5 ); sqlite> insert into m values (3, 2007, 5 ); sqlite> insert into m values (4, 2007, 6 ); sqlite> sqlite> select count(*) from ( select * from m group by year, month ); 3 sqlite> Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] select COUNT (DISTINCT column1, column2) from table?
Hi Phani,
Hope this answers your Query.
SQLite version 3.1.3
Enter ".help" for instructions
sqlite> create table m( mNo integer, year integer, month integer );
sqlite> insert into m values (1, 2007, 9);
sqlite> insert into m values (2, 2006, 5);
sqlite> insert into m values (3, 2006, 5);
sqlite> insert into m values (4, 2004, 4);
sqlite> SELECT COUNT(*) FROM ( SELECT COUNT(*) FROM m group by year,month);
3
sqlite>
Regards,
Bharath
On 9/25/07 3:59 PM, "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:
> Hi Simon,
>
> Assume you have a following data:
> matchNo, year, month
> 34 2007 9
>
> 27 2006 5
>
> 26 2006 5
>
> 24 2005 4
>
> For the above data my answer should be 3, since there are three unique
> combination of year, month {(2007, 9), (2006, 5), (2005, 4)}. Here I
> need to find the number of distinct combinations of year, month not the
> count for a particular year, month.
>
> Regards,
> Phani
>
>
> -Original Message-
> From: Simon Davies [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, September 25, 2007 3:14 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] select COUNT (DISTINCT column1, column2) from
> table?
>
> On 25/09/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
> .
> .
>> Assume you have a following data:
>>
>> matchNo, year, month
>>
>> 34 2007 9
>>
>> 27 2006 5
>>
>> 26 2006 5
>>
>> Now distinct year, month will return
>>
>> 2007, 9
>>
>> 2006, 5
>>
>> Is there a way by which I can count (distinct year, month)
> combinations?
>> For this example answer should be 2.
>>
>> Regards,
>>
>> Phani
>
> Hi Phani,
>
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite>
> sqlite> create table m( mNo integer, year integer, month integer );
> sqlite>
> sqlite> insert into m values (1, 2006, 11 );
> sqlite> insert into m values (2, 2007, 5 );
> sqlite> insert into m values (3, 2007, 5 );
> sqlite>
> sqlite> select count(*), year, month from m group by year,month;
> 1|2006|11
> 2|2007|5
> sqlite>
>
> Rgds,
> Simon
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
---
Robosoft Technologies - Come home to Technology
Disclaimer: This email may contain confidential material. If you were not an
intended recipient, please notify the sender and delete all copies. Emails to
and from our network may be logged and monitored. This email and its
attachments are scanned for virus by our scanners and are believed to be safe.
However, no warranty is given that this email is free of malicious content or
virus.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] select COUNT (DISTINCT column1, column2) from table?
Hi Simon,
Assume you have a following data:
matchNo, year, month
34 2007 9
27 2006 5
26 2006 5
24 2005 4
For the above data my answer should be 3, since there are three unique
combination of year, month {(2007, 9), (2006, 5), (2005, 4)}. Here I
need to find the number of distinct combinations of year, month not the
count for a particular year, month.
Regards,
Phani
-Original Message-
From: Simon Davies [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 25, 2007 3:14 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] select COUNT (DISTINCT column1, column2) from
table?
On 25/09/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
.
.
> Assume you have a following data:
>
> matchNo, year, month
>
> 34 2007 9
>
> 27 2006 5
>
> 26 2006 5
>
> Now distinct year, month will return
>
> 2007, 9
>
> 2006, 5
>
> Is there a way by which I can count (distinct year, month)
combinations?
> For this example answer should be 2.
>
> Regards,
>
> Phani
Hi Phani,
SQLite version 3.4.2
Enter ".help" for instructions
sqlite>
sqlite> create table m( mNo integer, year integer, month integer );
sqlite>
sqlite> insert into m values (1, 2006, 11 );
sqlite> insert into m values (2, 2007, 5 );
sqlite> insert into m values (3, 2007, 5 );
sqlite>
sqlite> select count(*), year, month from m group by year,month;
1|2006|11
2|2007|5
sqlite>
Rgds,
Simon
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] select COUNT (DISTINCT column1, column2) from table?
On 25/09/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote: . . > Assume you have a following data: > > matchNo, year, month > > 34 2007 9 > > 27 2006 5 > > 26 2006 5 > > Now distinct year, month will return > > 2007, 9 > > 2006, 5 > > Is there a way by which I can count (distinct year, month) combinations? > For this example answer should be 2. > > Regards, > > Phani Hi Phani, SQLite version 3.4.2 Enter ".help" for instructions sqlite> sqlite> create table m( mNo integer, year integer, month integer ); sqlite> sqlite> insert into m values (1, 2006, 11 ); sqlite> insert into m values (2, 2007, 5 ); sqlite> insert into m values (3, 2007, 5 ); sqlite> sqlite> select count(*), year, month from m group by year,month; 1|2006|11 2|2007|5 sqlite> Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] select COUNT (DISTINCT column1, column2) from table?
Assume I have a database. Maintable (matchNo integer, year INTEGER, month INTEGER) I have to find the count of distinct year, month combinations in which matches were played. I tried the query select COUNT (DISTINCT column1, column2) from table but this gives an error. I would like to know is there a simple query by which one can figure out this. Assume you have a following data: matchNo, year, month 34 2007 9 27 2006 5 26 2006 5 Now distinct year, month will return 2007, 9 2006, 5 Is there a way by which I can count (distinct year, month) combinations? For this example answer should be 2. Regards, Phani
