Thread: Any ideas why this doesn't work or how to rewrite it?
This seems like a simple SQL command, but I'm getting errors. Running 6.5.0 under RedHat 5.2: db=> select count(distinct customer_username) from customerdata; ERROR: parser: parse error at or near "distinct" How do you get a count of distinct data output via postgres? I can always just count the number of tuples returned but this seemed to be a valid query. -------------------------------------------------------------------------- Aaron Holtz ComNet Inc. UNIX Systems Specialist Email: aholtz@bright.net "It's not broken, it just lacks duct tape." --------------------------------------------------------------------------
On Wed, 28 Apr 1999, Aaron Holtz wrote:
> This seems like a simple SQL command, but I'm getting errors.
> Running 6.5.0 under RedHat 5.2:
>
> db=> select count(distinct customer_username) from customerdata;
> ERROR: parser: parse error at or near "distinct"
>
> How do you get a count of distinct data output via postgres? I can always
> just count the number of tuples returned but this seemed to be a valid
> query.
In SQL, it is just pretty valid query. But Postgres does not implement
SELECT COUNT(DISTINCT).
Do instead
SELECT DISTINCT(customer_username) FROM customerdata;
and count it in your program.
> --------------------------------------------------------------------------
> Aaron Holtz
> ComNet Inc.
> UNIX Systems Specialist
> Email: aholtz@bright.net
> "It's not broken, it just lacks duct tape."
> --------------------------------------------------------------------------
Oleg.
----
Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net
Programmers don't die, they just GOSUB without RETURN.
Yes, that is what I'll have to do. Just counting the number of tuples returned from this query would work. I was just wondering why this wasn't implemented in postgres or what the valid syntax would be. Thanks! -------------------------------------------------------------------------- Aaron Holtz ComNet Inc. UNIX Systems Specialist Email: aholtz@bright.net "It's not broken, it just lacks duct tape." -------------------------------------------------------------------------- On Apr 28, Oleg Broytmann molded the electrons to say.... >On Wed, 28 Apr 1999, Aaron Holtz wrote: >> This seems like a simple SQL command, but I'm getting errors. >> Running 6.5.0 under RedHat 5.2: >> >> db=> select count(distinct customer_username) from customerdata; >> ERROR: parser: parse error at or near "distinct" >> >> How do you get a count of distinct data output via postgres? I can always >> just count the number of tuples returned but this seemed to be a valid >> query. > > In SQL, it is just pretty valid query. But Postgres does not implement >SELECT COUNT(DISTINCT). > > Do instead >SELECT DISTINCT(customer_username) FROM customerdata; > and count it in your program. > >> -------------------------------------------------------------------------- >> Aaron Holtz >> ComNet Inc. >> UNIX Systems Specialist >> Email: aholtz@bright.net >> "It's not broken, it just lacks duct tape." >> -------------------------------------------------------------------------- > >Oleg. >---- > Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net > Programmers don't die, they just GOSUB without RETURN. >
> This seems like a simple SQL command, but I'm getting errors. > Running 6.5.0 under RedHat 5.2: > > db=> select count(distinct customer_username) from customerdata; > ERROR: parser: parse error at or near "distinct" > > How do you get a count of distinct data output via postgres? SELECT customer_username, COUNT(customer_username) FROM customerdata GROUP BY customer_username;
Hey there... > SELECT customer_username, COUNT(customer_username) FROM customerdata GROUP BY customer_username; > The above query, in effect, does the same thing that: SELECT DISTINCT(customer_username) from customerdata does. In order to get the total number of distinct customer_usernames, you would still have to count the rows returned (which is easily enough done with PHP $count = pg_NumRows($query_result)). My guess is that SELECT DISTINCT might even be a bit quicker...?? (gurus) > Bob
Yes, I'm just taking the value ntuples returned after doing: select distinct customer_username from customerdata group by customer_username; I guess my big thing was that I saw a couple of select count(distinct something) from table; examples in an SQL book that I have. Was just curious as to whether psql didn't support this type of command or decided that a better way existed to write the query. Thanks to all for the thoughts and ideas. Maybe this support will be added at some juncture. -------------------------------------------------------------------------- Aaron Holtz ComNet Inc. UNIX Systems Specialist Email: aholtz@bright.net "It's not broken, it just lacks duct tape." -------------------------------------------------------------------------- On Wed, 28 Apr 1999, Bob Dusek wrote: >Hey there... > >> SELECT customer_username, COUNT(customer_username) FROM customerdata GROUP BY customer_username; >> > >The above query, in effect, does the same thing that: > >SELECT DISTINCT(customer_username) from customerdata > >does. In order to get the total number of distinct customer_usernames, >you would still have to count the rows returned (which is easily >enough done with PHP $count = pg_NumRows($query_result)). > >My guess is that SELECT DISTINCT might even be a bit quicker...?? (gurus) > >> > >Bob >
At 18:50 +0300 on 28/04/1999, Aaron Holtz wrote: > db=> select count(distinct customer_username) from customerdata; > ERROR: parser: parse error at or near "distinct" > > How do you get a count of distinct data output via postgres? I can always > just count the number of tuples returned but this seemed to be a valid > query. Valid it is, but not yet supported in PostgreSQL. An (ugly) workaround would be something along the lines of: SELECT count(customer_username) FROM customerdata c1 WHERE int( oid ) = ( SELECT min( int( c2.oid ) ) FROM customerdata c2 WHERE c1.customer_username = c2.customer_username ); The WHERE clause causes only rows whose OIDs are the minimal for the current username to be selected. Thus only one row is selected for each username. And this is countable. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
On Thu, 29 Apr 1999, Herouth Maoz wrote:
> At 18:50 +0300 on 28/04/1999, Aaron Holtz wrote:
>
>
> > db=> select count(distinct customer_username) from customerdata;
> > ERROR: parser: parse error at or near "distinct"
> >
> > How do you get a count of distinct data output via postgres? I can always
> > just count the number of tuples returned but this seemed to be a valid
> > query.
>
> Valid it is, but not yet supported in PostgreSQL.
>
> An (ugly) workaround would be something along the lines of:
>
> SELECT count(customer_username)
> FROM customerdata c1
> WHERE int( oid ) = (
> SELECT min( int( c2.oid ) )
> FROM customerdata c2
> WHERE c1.customer_username = c2.customer_username
> );
I think, Aaron, you could get a count of distinct customer names like this:
SELECT DISTINCT customer_username, COUNT(*) FROM customerdata
GROUP BY customer_username;
This will give you 2 columns, one with the distinct customer_usernames
and the second with the count of each. The GROUP BY caluse is important
here. This looks like what you wanted in your original query.
Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
Schapiro's Explanation:
The grass is always greener on the other side -- but that's
because they use more manure.
At 21:04 +0300 on 29/04/1999, Brett W. McCoy wrote:
> I think, Aaron, you could get a count of distinct customer names like this:
>
> SELECT DISTINCT customer_username, COUNT(*) FROM customerdata
> GROUP BY customer_username;
>
> This will give you 2 columns, one with the distinct customer_usernames
> and the second with the count of each. The GROUP BY caluse is important
> here. This looks like what you wanted in your original query.
No, Brett. COUNT( DISTINCT ...) is supposed to count the number of distinct
names in a table. Here, I created a test table:
testing=> select * from test;
customer
--------
moshe
david
hanna
david
sarah
moshe
suzanne
moshe
moshe
(9 rows)
The distinct names are:
testing=> select distinct customer
testing-> from test;
customer
--------
david
hanna
moshe
sarah
suzanne
(5 rows)
So clearly, the datum he wanted was "5" - there are five distinct customers
here.
Your query, however, gives the following:
testing=> select distinct customer, count(*)
testing-> from test
testing-> group by customer;
customer|count
--------+-----
david | 2
hanna | 1
moshe | 4
sarah | 1
suzanne | 1
(5 rows)
Which shows him the number of REPETITIONS on each distinct name.
My ugly query gives:
testing=> select count(*)
testing-> from test t1
testing-> where int( oid ) = (
testing-> SELECT min( int( t2.oid ) )
testing-> FROM test t2
testing-> WHERE t2.customer = t1.customer
testing-> );
count
-----
5
(1 row)
And this is the exact number of distinct names in the table.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma