Skip site navigation (1) Skip section navigation (2)

Re: Query how-to

From: "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de>
To: "Montaseri" <montaseri(at)gmail(dot)com>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Query how-to
Date: 2008-10-03 20:29:12
Message-ID: CA896D7906BF224F8A6D74A1B7E54AB3039E8BEE@JENMAIL01.ad.intershop.net (view raw or flat)
Thread:
Lists: pgsql-sql
this was silly from me!
 
this should naturally look like this:
 

  select case when status ='Closed' then stop_date else start_date end
as adate,
         sum(case when status ='Closed' then 1 else 0 end) as
closedCount,
         sum(case when status ='New' then 1 else 0 end) as openedCount
  from  Yourtable
  where status in ('Closed','New')
  group by case when status ='Closed' then stop_date else start_date end

  order by adate

Marc

> Hi,

> What about something like that ?

> select adate, sum(openedCount) as openedCount, sum(closedCount) as
closedCount
> from
> (
>   select sum(case when status ='Closed' then stop_date else start_date
end) as adate,
>        sum(case when status ='Closed' then 1 else 0 end) as
closedCount
>        sum(case when status ='New' then 1 else 0 end) as openedCount
>   from  Yourtable
>   where status in ('Closed','New')
> )x
> group by adate
> order by adate

In response to

pgsql-sql by date

Next:From: Louis-David MitterrandDate: 2008-10-06 13:08:02
Subject: many-to-many relationship
Previous:From: Mark RobertsDate: 2008-10-03 19:57:19
Subject: Re: For each key, find row with highest value of other field

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group