Query to Find number of distinct records

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Query to Find number of distinct records

Bharath Booshan L
Hello All,

 This might be simple question, but am not getting the SQL query right for
my problem.


For eg, consider following table

NonUniqueNo  Name
-----------------
23             A
23             B
24             C
25             A
23             E


How can I find the number of people for which an entry has been recorded in
my table?

The answer I am expecting is 4 ( i.e. A,B,C,E)

Using query "select count(DISTINCT Name) from TableA" results in an error.

Could anyone please post a SQL query for my problem?

Thanks in advance,

Bharath



-----------------------------------------------
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.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query to Find number of distinct records

Shibu.Narayanan
Try
select count(DISTINCT Name) nameCount from TableA

Shibu Narayanan
Consultant, PrimeSourcing Division, Investment Banking Group
Tel.Office: 91-80-2208-6270 or 91-80-6659-6270
e-mail: [hidden email]
The answer is 42.

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Bharath Booshan L
Sent: Wednesday, February 27, 2008 12:35 PM
To: Discussion of SQLite Database
Subject: [sqlite] Query to Find number of distinct records

Hello All,

 This might be simple question, but am not getting the SQL query right
for
my problem.


For eg, consider following table

NonUniqueNo  Name
-----------------
23             A
23             B
24             C
25             A
23             E


How can I find the number of people for which an entry has been recorded
in
my table?

The answer I am expecting is 4 ( i.e. A,B,C,E)

Using query "select count(DISTINCT Name) from TableA" results in an
error.

Could anyone please post a SQL query for my problem?

Thanks in advance,

Bharath



-----------------------------------------------
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.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


DISCLAIMER:
This message contains privileged and confidential information and is intended only for an individual named. If you are not the intended recipient, you should not disseminate, distribute, store, print, copy or deliver this message. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete or contain viruses. The sender, therefore,  does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required, please request a hard-copy version.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query to Find number of distinct records

Bharath Booshan L
Thanks for your quick response,

> select count(DISTINCT Name) nameCount from TableA

This is not working :(. I am getting the same error.

--
Bharath



On 2/27/08 12:38 PM, "[hidden email]"
<[hidden email]> wrote:

> Try
> select count(DISTINCT Name) nameCount from TableA
>
> Shibu Narayanan
> Consultant, PrimeSourcing Division, Investment Banking Group
> Tel.Office: 91-80-2208-6270 or 91-80-6659-6270
> e-mail: [hidden email]
> The answer is 42.
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On Behalf Of Bharath Booshan L
> Sent: Wednesday, February 27, 2008 12:35 PM
> To: Discussion of SQLite Database
> Subject: [sqlite] Query to Find number of distinct records
>
> Hello All,
>
>  This might be simple question, but am not getting the SQL query right
> for
> my problem.
>
>
> For eg, consider following table
>
> NonUniqueNo  Name
> -----------------
> 23             A
> 23             B
> 24             C
> 25             A
> 23             E
>
>
> How can I find the number of people for which an entry has been recorded
> in
> my table?
>
> The answer I am expecting is 4 ( i.e. A,B,C,E)
>
> Using query "select count(DISTINCT Name) from TableA" results in an
> error.
>
> Could anyone please post a SQL query for my problem?
>
> Thanks in advance,
>
> Bharath
>
>
>
> -----------------------------------------------
> 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.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> DISCLAIMER:
> This message contains privileged and confidential information and is intended
> only for an individual named. If you are not the intended recipient, you
> should not disseminate, distribute, store, print, copy or deliver this
> message. Please notify the sender immediately by e-mail if you have received
> this e-mail by mistake and delete this e-mail from your system. E-mail
> transmission cannot be guaranteed to be secure or error-free as information
> could be intercepted, corrupted, lost, destroyed, arrive late or incomplete or
> contain viruses. The sender, therefore,  does not accept liability for any
> errors or omissions in the contents of this message which arise as a result of
> e-mail transmission. If verification is required, please request a hard-copy
> version.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-----------------------------------------------
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.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query to Find number of distinct records

Bharath Booshan L
In reply to this post by Bharath Booshan L
Yeah!! I got it right this time.

Select count(*) from ( select DISTINCT Name from TableA);


But what's not getting into my mind is the difference b/w the following two
queries:

Select count( Name) from TableA  -- works fine

Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?


On 2/27/08 12:34 PM, "Bharath Booshan L" <[hidden email]>
wrote:

> Hello All,
>
>  This might be simple question, but am not getting the SQL query right for
> my problem.
>
>
> For eg, consider following table
>
> NonUniqueNo  Name
> -----------------
> 23             A
> 23             B
> 24             C
> 25             A
> 23             E
>
>
> How can I find the number of people for which an entry has been recorded in
> my table?
>
> The answer I am expecting is 4 ( i.e. A,B,C,E)
>
> Using query "select count(DISTINCT Name) from TableA" results in an error.
>
> Could anyone please post a SQL query for my problem?
>
> Thanks in advance,
>
> Bharath
>
>
>
> -----------------------------------------------
> 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.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-----------------------------------------------
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.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query to Find number of distinct records

Eugene Wee
In reply to this post by Bharath Booshan L
Hi,

What is the error?

It seems to me that both of these should work:
select count(DISTINCT Name) from TableA
select count(DISTINCT Name) as nameCount from TableA

Regards,
Eugene Wee


Bharath Booshan L wrote:
> Thanks for your quick response,
>
>> select count(DISTINCT Name) nameCount from TableA
>
> This is not working :(. I am getting the same error.
>
> --
> Bharath
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query to Find number of distinct records

Eugene Wee
In reply to this post by Bharath Booshan L
Hi,

Bharath Booshan L wrote:

> Yeah!! I got it right this time.
>
> Select count(*) from ( select DISTINCT Name from TableA);
>
>
> But what's not getting into my mind is the difference b/w the following two
> queries:
>
> Select count( Name) from TableA  -- works fine
>
> Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?

The thing is, it should work.

Regards,
Eugene Wee
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

FW: Query to Find number of distinct records

Bharath Booshan L
In reply to this post by Bharath Booshan L

------ Forwarded Message
From: Bharath Booshan L <[hidden email]>
Date: Wed, 27 Feb 2008 14:32:14 +0530
To: Eugene Wee <[hidden email]>
Conversation: [sqlite] Query to Find number of distinct records
Subject: Re: [sqlite] Query to Find number of distinct records

I was off for lunch

> The thing is, it should work. What is the error message?

sqlite> select count(DISTINCT Name) as nameCount from TableA;
SQL error: near "DISTINCT": syntax error


On 2/27/08 1:24 PM, "Eugene Wee" <[hidden email]> wrote:

> Hi,
>
> Bharath Booshan L wrote:
>> Yeah!! I got it right this time.
>>
>> Select count(*) from ( select DISTINCT Name from TableA);
>>
>>
>> But what's not getting into my mind is the difference b/w the following two
>> queries:
>>
>> Select count( Name) from TableA  -- works fine
>>
>> Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?
>
> The thing is, it should work. What is the error message?
>
> Regards,
> Eugene Wee
>

------ End of Forwarded Message



-----------------------------------------------
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.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: FW: Query to Find number of distinct records

SimonDavies
Hi Bharath,

Works for me in 3.4.2

SQLite version 3.4.2
Enter ".help" for instructions
sqlite> create table tst( nun integer, nm text );
sqlite> insert into tst values( 23, 'A' );
sqlite> insert into tst values( 23, 'b' );
sqlite> insert into tst values( 24, 'C' );
sqlite> insert into tst values( 25, 'A' );
sqlite> insert into tst values( 23, 'E' );
sqlite> select count( distinct nm ) from tst;
4

Rgds,
Simon


On 27/02/2008, Bharath Booshan L <[hidden email]> wrote:

>
> ------ Forwarded Message
> From: Bharath Booshan L <[hidden email]>
> Date: Wed, 27 Feb 2008 14:32:14 +0530
> To: Eugene Wee <[hidden email]>
> Conversation: [sqlite] Query to Find number of distinct records
> Subject: Re: [sqlite] Query to Find number of distinct records
>
> I was off for lunch
>
> > The thing is, it should work. What is the error message?
>
> sqlite> select count(DISTINCT Name) as nameCount from TableA;
> SQL error: near "DISTINCT": syntax error
>
>
> On 2/27/08 1:24 PM, "Eugene Wee" <[hidden email]> wrote:
>
> > Hi,
> >
> > Bharath Booshan L wrote:
> >> Yeah!! I got it right this time.
> >>
> >> Select count(*) from ( select DISTINCT Name from TableA);
> >>
> >>
> >> But what's not getting into my mind is the difference b/w the following two
> >> queries:
> >>
> >> Select count( Name) from TableA  -- works fine
> >>
> >> Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?
> >
> > The thing is, it should work. What is the error message?
> >
> > Regards,
> > Eugene Wee
> >
>
> ------ End of Forwarded Message
>
>
>
> -----------------------------------------------
> 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.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: FW: Query to Find number of distinct records

cscetbon
In reply to this post by Bharath Booshan L
support for count(distinct) has been added in 3.2.6
your version is certainly < 3.2.6 (sqlite -version)

Bharath Booshan L wrote:

> ------ Forwarded Message
> From: Bharath Booshan L <[hidden email]>
> Date: Wed, 27 Feb 2008 14:32:14 +0530
> To: Eugene Wee <[hidden email]>
> Conversation: [sqlite] Query to Find number of distinct records
> Subject: Re: [sqlite] Query to Find number of distinct records
>
> I was off for lunch
>
>> The thing is, it should work. What is the error message?
>
> sqlite> select count(DISTINCT Name) as nameCount from TableA;
> SQL error: near "DISTINCT": syntax error
>
>
> On 2/27/08 1:24 PM, "Eugene Wee" <[hidden email]> wrote:
>
>> Hi,
>>
>> Bharath Booshan L wrote:
>>> Yeah!! I got it right this time.
>>>
>>> Select count(*) from ( select DISTINCT Name from TableA);
>>>
>>>
>>> But what's not getting into my mind is the difference b/w the following two
>>> queries:
>>>
>>> Select count( Name) from TableA  -- works fine
>>>
>>> Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?
>> The thing is, it should work. What is the error message?
>>
>> Regards,
>> Eugene Wee
>>
>
> ------ End of Forwarded Message
>
>
>
> -----------------------------------------------
> 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.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

--
Cyril SCETBON

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: FW: Query to Find number of distinct records

Bharath Booshan L
> support for count(distinct) has been added in 3.2.6
> your version is certainly < 3.2.6 (sqlite -version)
Your are right. My version is 3.1.3


Thanks a lot for all your inputs folks :-)

--
Bharath

On 2/27/08 4:31 PM, "Cyril SCETBON" <[hidden email]> wrote:

> support for count(distinct) has been added in 3.2.6
> your version is certainly < 3.2.6 (sqlite -version)
>
> Bharath Booshan L wrote:
>> ------ Forwarded Message
>> From: Bharath Booshan L <[hidden email]>
>> Date: Wed, 27 Feb 2008 14:32:14 +0530
>> To: Eugene Wee <[hidden email]>
>> Conversation: [sqlite] Query to Find number of distinct records
>> Subject: Re: [sqlite] Query to Find number of distinct records
>>
>> I was off for lunch
>>
>>> The thing is, it should work. What is the error message?
>>
>> sqlite> select count(DISTINCT Name) as nameCount from TableA;
>> SQL error: near "DISTINCT": syntax error
>>
>>
>> On 2/27/08 1:24 PM, "Eugene Wee" <[hidden email]> wrote:
>>
>>> Hi,
>>>
>>> Bharath Booshan L wrote:
>>>> Yeah!! I got it right this time.
>>>>
>>>> Select count(*) from ( select DISTINCT Name from TableA);
>>>>
>>>>
>>>> But what's not getting into my mind is the difference b/w the following two
>>>> queries:
>>>>
>>>> Select count( Name) from TableA  -- works fine
>>>>
>>>> Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?
>>> The thing is, it should work. What is the error message?
>>>
>>> Regards,
>>> Eugene Wee
>>>
>>
>> ------ End of Forwarded Message
>>
>>
>>
>> -----------------------------------------------
>> 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.
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>



-----------------------------------------------
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.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users