Thread: Question about a query with two count fields

Hello,

I am in need of producing a query that has two count fields in it... Something like:

select to_char(ts, 'MM/DD/YYYY') as "day", str, proc,
(select count (*) as good from foobar where z != 0),
(select count (*) as bad from foobar where z = 0)
from foobar
where str != 99999
group by str, day, proc order by str

From this query, the output should look something like this:

day            | str  |  proc  | good |  bad
09/10/2007 | 1    |  xyz   |    1   |   3
09/10/2007 | 1    |  abc   |    3   |   2
09/10/2007 | 2    |  xyz   |    3   |   5
09/10/2007 | 2    |  abc   |    1   |   2

I hope I have explained the situation...

I appreciate the help...

Thanks.


-Jeff

select
  to_char(ts, 'MM/DD/YYYY') as "day",
  str,
  proc,
  sum(case when z!=0 then 1 end) as good,
  sum(case when z =0 then 1 end) as bad
from foobar
where str != 99999
group by 1,2,3
order by 1
;


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeff
> Lanzarotta
> Sent: Tuesday, September 11, 2007 8:51 AM
> To: PostgreSQL GENERAL List
> Subject: [GENERAL] Question about a query with two count fields
>
> Hello,
>
> I am in need of producing a query that has two count fields
> in it... Something like:
>
> select to_char(ts, 'MM/DD/YYYY') as "day", str, proc,
> (select count (*) as good from foobar where z != 0),
> (select count (*) as bad from foobar where z = 0)
> from foobar
> where str != 99999
> group by str, day, proc order by str
>
> From this query, the output should look something like this:
>
> day            | str  |  proc  | good |  bad
> 09/10/2007 | 1    |  xyz   |    1   |   3
> 09/10/2007 | 1    |  abc   |    3   |   2
> 09/10/2007 | 2    |  xyz   |    3   |   5
> 09/10/2007 | 2    |  abc   |    1   |   2
>
> I hope I have explained the situation...
>
> I appreciate the help...
>
> Thanks.
>
>
> -Jeff
>

On 9/11/07, Jeff Lanzarotta <delux256-postgresql@yahoo.com> wrote:
> I appreciate the help...

SELECT   TO_CHAR(ts, 'MM/DD/YYYY') AS "day", str, proc
       , SUM(CASE
               WHEN z <> 0
                 THEN 1
               ELSE 0
             END) AS good, 0 AS ajaaaaaaaaaaaaaa
       , SUM(CASE
               WHEN z = 0
                 THEN 1
               ELSE 0
             END) AS bad
    FROM foobar
   WHERE str <> 99999
GROUP BY str, DAY, proc
ORDER BY str

Remove the ", 0 AS ajaaaaaaaaaaaaaa", that was some filler that got
thru by mistake.

On Tue, Sep 11, 2007 at 08:55:53AM -0700, George Pavlov wrote:
> select
>   to_char(ts, 'MM/DD/YYYY') as "day",
>   str,
>   proc,
>   sum(case when z!=0 then 1 end) as good,

This case statement returns true when z factorial is zero, so I'd
recommend the SQL standard <> or IS NOT DISTINCT FROM instead.

>   sum(case when z =0 then 1 end) as bad
> from foobar
> where str != 99999

This may parse differently, but <> is more cautious.

Cheers,
David.

> group by 1,2,3
> order by 1
> ;
>
>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeff
> > Lanzarotta
> > Sent: Tuesday, September 11, 2007 8:51 AM
> > To: PostgreSQL GENERAL List
> > Subject: [GENERAL] Question about a query with two count fields
> >
> > Hello,
> >
> > I am in need of producing a query that has two count fields
> > in it... Something like:
> >
> > select to_char(ts, 'MM/DD/YYYY') as "day", str, proc,
> > (select count (*) as good from foobar where z != 0),
> > (select count (*) as bad from foobar where z = 0)
> > from foobar
> > where str != 99999
> > group by str, day, proc order by str
> >
> > From this query, the output should look something like this:
> >
> > day            | str  |  proc  | good |  bad
> > 09/10/2007 | 1    |  xyz   |    1   |   3
> > 09/10/2007 | 1    |  abc   |    3   |   2
> > 09/10/2007 | 2    |  xyz   |    3   |   5
> > 09/10/2007 | 2    |  abc   |    1   |   2
> >
> > I hope I have explained the situation...
> >
> > I appreciate the help...
> >
> > Thanks.
> >
> >
> > -Jeff
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

Awesome, thanks...

George Pavlov <gpavlov@mynewplace.com> wrote:

select
to_char(ts, 'MM/DD/YYYY') as "day",
str,
proc,
sum(case when z!=0 then 1 end) as good,
sum(case when z =0 then 1 end) as bad
from foobar
where str != 99999
group by 1,2,3
order by 1
;


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeff
> Lanzarotta
> Sent: Tuesday, September 11, 2007 8:51 AM
> To: PostgreSQL GENERAL List
> Subject: [GENERAL] Question about a query with two count fields
>
> Hello,
>
> I am in need of producing a query that has two count fields
> in it... Something like:
>
> select to_char(ts, 'MM/DD/YYYY') as "day", str, proc,
> (select count (*) as good from foobar where z != 0),
> (select count (*) as bad from foobar where z = 0)
> from foobar
> where str != 99999
> group by str, day, proc order by str
>
> From this query, the output should look something like this:
>
> day | str | proc | good | bad
> 09/10/2007 | 1 | xyz | 1 | 3
> 09/10/2007 | 1 | abc | 3 | 2
> 09/10/2007 | 2 | xyz | 3 | 5
> 09/10/2007 | 2 | abc | 1 | 2
>
> I hope I have explained the situation...
>
> I appreciate the help...
>
> Thanks.
>
>
> -Jeff
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> From: David Fetter [mailto:david@fetter.org]
> On Tue, Sep 11, 2007 at 08:55:53AM -0700, George Pavlov wrote:
> >   sum(case when z!=0 then 1 end) as good,
>
> This case statement returns true when z factorial is zero, so I'd
> recommend the SQL standard <> or IS NOT DISTINCT FROM instead.

and what would be that number that has its factorial = 0 ?! (just giving
you a hard time this not being a math forum...)

thanks dave, you do bring a valid point of there being ambiguity about
the ! and that might be worth noting in the manual
(http://www.postgresql.org/docs/8.2/interactive/functions-comparison.htm
l). however, it seems that our favorite DBMS does the right thing and
gives precedence to the != as "not equal", so taking a more realistic
example (non-zero factorials):

foo=> select 2 != 2;
 ?column?
----------
 f
(1 row)

foo=> select 2 <> 2;
 ?column?
----------
 f
(1 row)

foo=> select (2 !)= 2;
 ?column?
----------
 t
(1 row)

i do hate potential ambiguity... the != was something stuck in my brain
from old sybase, i think. i always liked != ("not equals") as more
intuitive than <> ("less than, greater than"???), but i will have to
change my ways, especially if "the standard" says so.

"George Pavlov" <gpavlov@mynewplace.com> writes:
>> From: David Fetter [mailto:david@fetter.org]
>> This case statement returns true when z factorial is zero, so I'd
>> recommend the SQL standard <> or IS NOT DISTINCT FROM instead.

> i do hate potential ambiguity... the != was something stuck in my brain
> from old sybase, i think. i always liked != ("not equals") as more
> intuitive than <> ("less than, greater than"???), but i will have to
> change my ways, especially if "the standard" says so.

The notion that != might be scanned as two operators whereas <> would
not be is nonsense.  I assume David was just joking.

            regards, tom lane

On Tue, Sep 11, 2007 at 02:28:24PM -0400, Tom Lane wrote:
> "George Pavlov" <gpavlov@mynewplace.com> writes:
> >> From: David Fetter [mailto:david@fetter.org]
> >> This case statement returns true when z factorial is zero, so I'd
> >> recommend the SQL standard <> or IS NOT DISTINCT FROM instead.
>
> > i do hate potential ambiguity... the != was something stuck in my brain
> > from old sybase, i think. i always liked != ("not equals") as more
> > intuitive than <> ("less than, greater than"???), but i will have to
> > change my ways, especially if "the standard" says so.
>
> The notion that != might be scanned as two operators whereas <> would
> not be is nonsense.  I assume David was just joking.

My mistake.

I believe that foo!=bar without white space should simply error out
because there is no reasonable, unambiguous way to parse it.  Here's
what we get right now:

test=# SELECT (2! =0);
 ?column?
----------
 f
(1 row)

test=# SELECT (2!=0);
 ?column?
----------
 t
(1 row)

test=# SELECT (2 !=0);
 ?column?
----------
 t
(1 row)

test=# SELECT (2 ! =0);
 ?column?
----------
 f
(1 row)

test=# SELECT (2 != 0);
 ?column?
----------
 t
(1 row)

test=# SELECT (2 ! = 0);
 ?column?
----------
 f
(1 row)

--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

On Sep 11, 2007, at 13:42 , David Fetter wrote:

> I believe that foo!=bar without white space should simply error out
> because there is no reasonable, unambiguous way to parse it.  Here's
> what we get right now:

What's ambigious about it? An operator cannot include a space, so !=
(no space) is *always* interpreted as one operator: not equals (<>).

space (! and = are separate operators), so ! is factorial.

> test=# SELECT (2! =0);
>  ?column?
> ----------
>  f
> (1 row)

> test=# SELECT (2 ! =0);
>  ?column?
> ----------
>  f
> (1 row)

> test=# SELECT (2 ! = 0);
>  ?column?
> ----------
>  f
> (1 row)
>

no space, so != is one operator,

> test=# SELECT (2!=0);
>  ?column?
> ----------
>  t
> (1 row)

> test=# SELECT (2 != 0);
>  ?column?
> ----------
>  t
> (1 row)
>

> test=# SELECT (2 !=0);
>  ?column?
> ----------
>  t
> (1 row)

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann <grzm@seespotcode.net> writes:
> What's ambigious about it? An operator cannot include a space, so !=
> (no space) is *always* interpreted as one operator: not equals (<>).

Right.  There are some corner cases though, for example

    A*-5

which you'd probably rather weren't interpreted as a single operator
"*-".  I believe the hack we use for this involves parsing a trailing
"+" or "-" as a separate operator if the earlier part of the operator
name contains only a certain set of characters.  The grotty details
are in the manual ...

            regards, tom lane