What's wrong with this query that used to work great:

webunl=> SELECT DISTINCT count(*),id_curso AS LINK,titulo AS 
TITULO_CARRERA,admin_view_facultades.nombre AS FACULTAD,
webunl-> duracion/12 AS DURACION FROM admin_view, admin_view_facultades
webunl-> WHERE admin_view_facultades.id_carrera=admin_view.id_curso
webunl-> GROUP BY id_curso,titulo,admin_view_facultades.nombre,duracion/12
webunl-> ORDER BY id_curso ASC  LIMIT 20 OFFSET 0 ;
 count | link |       titulo_carrera        |            facultad            
| duracion
-------+------+-----------------------------+--------------------------------+----------
     1 |    1 | Ingenieria en informatica   | Facultad de Ingenier<ED>a 
Qu<ED>mica |        4
     1 |    2 | Licenciatura en Matematicas | Facultad de Ingenier<ED>a 
Qu<ED>mica |        4
(2 rows)
 
webunl=>

As you can see count(*) is 1, even seeing 2 rows selected. What's wrong?
All that chenged is that I recompiled postgreSQL 7.1.2 with the patch from 
the rewriteHandler.c file.

-- 
Porquï¿½ usar una base de datos relacional cualquiera,
si podï¿½s usar PostgreSQL?
-----------------------------------------------------------------
Martï¿½n Marquï¿½s                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Fri, 10 Aug 2001 20:58:54 -0300
=?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= <martin@bugs.unl.edu.ar>
Bug?
=?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= <martin@bugs.unl.edu.ar> writes:
> What's wrong with this query that used to work great:

Impossible to tell, with that amount of information.

> As you can see count(*) is 1, even seeing 2 rows selected. What's wrong?

Since it's a GROUP BY query, that's not necessarily wrong.  The counts
are the number of input rows in each group.

			regards, tom lane

Fri, 10 Aug 2001 20:53:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug? 
Looks like it works as it should.  The count(*) function is returning the number of records
in each group by condition, or '1' for each row. The count(*) does not return the total
number of rows returned by the query.

Dwayne

Martï¿½n Marquï¿½s wrote:

> What's wrong with this query that used to work great:
>
> webunl=> SELECT DISTINCT count(*),id_curso AS LINK,titulo AS
> TITULO_CARRERA,admin_view_facultades.nombre AS FACULTAD,
> webunl-> duracion/12 AS DURACION FROM admin_view, admin_view_facultades
> webunl-> WHERE admin_view_facultades.id_carrera=admin_view.id_curso
> webunl-> GROUP BY id_curso,titulo,admin_view_facultades.nombre,duracion/12
> webunl-> ORDER BY id_curso ASC  LIMIT 20 OFFSET 0 ;
>  count | link |       titulo_carrera        |            facultad
> | duracion
> -------+------+-----------------------------+--------------------------------+----------
>      1 |    1 | Ingenieria en informatica   | Facultad de Ingenier<ED>a
> Qu<ED>mica |        4
>      1 |    2 | Licenciatura en Matematicas | Facultad de Ingenier<ED>a
> Qu<ED>mica |        4
> (2 rows)
>
> webunl=>
>
> As you can see count(*) is 1, even seeing 2 rows selected. What's wrong?
> All that chenged is that I recompiled postgreSQL 7.1.2 with the patch from
> the rewriteHandler.c file.
>
> --
> Porquï¿½ usar una base de datos relacional cualquiera,
> si podï¿½s usar PostgreSQL?
> -----------------------------------------------------------------
> Martï¿½n Marquï¿½s                  |        mmarques@unl.edu.ar
> Programador, Administrador, DBA |       Centro de Telematica
>                        Universidad Nacional
>                             del Litoral
> -----------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


Fri, 10 Aug 2001 21:53:28 -0400
"P. Dwayne Miller" <dmiller@espgroup.net>
Re: Bug?