Question about a query with two count fields

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
;

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