Обсуждение: 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
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:
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