On Thu, 07 Nov 2002 09:57:27 +0100
Christoph Haller <[EMAIL PROTECTED]> wrote:
> > ORDER BY proj.project_id ;
>
> What about simply replacing ORDER BY proj.project_id ; by
> GROUP BY project_id, marketing_name ;
You're right. Thanks a lot.
Regards,
Masaru Sugawara
>
> This is the final query, can anyone see anything wrong with it?:
> SELECT projects.project_id, projects.marketing_name,
> COUNT(lots.lot_id) AS def_count,
> COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-08'}
> THEN lots.lot_id ELSE NULL END
>
e-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Christoph Haller
> Sent: Thursday, November 07, 2002 3:57 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] query optimization question
>
>
> > SELECT
> > project_
60 >=60def total
X 1 2 1 4 5 10 5 20 (if X had 4
lots, each of 5 deficiencies)
Y 1 1 0 2 3 3 0 6 (each has eg 3
deficiencies in project Y)
Terry Fielder
Network Engineer
Great Gulf H
> SELECT
> project_id,
> marketing_name,
> COUNT(lots.lot_id) AS def_count,
> COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-07'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_less_30,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-07'}
Wednesday, November 06, 2002 11:44 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] query optimization question
>
>
> On Wed, 6 Nov 2002 09:01:49 -0500
> <[EMAIL PROTECTED]> wrote:
>
> > If anyone can see a way to do a group by to do this
On Thu, 07 Nov 2002 01:44:25 +0900
I wrote <[EMAIL PROTECTED]> wrote:
> On Wed, 6 Nov 2002 09:01:49 -0500
> <[EMAIL PROTECTED]> wrote:
>
> > If anyone can see a way to do a group by to do this, then I will be happy to
> > hear about it, because currently the resultset has to do a separate
> > (s
On Wed, 6 Nov 2002 09:01:49 -0500
<[EMAIL PROTECTED]> wrote:
> If anyone can see a way to do a group by to do this, then I will be happy to
> hear about it, because currently the resultset has to do a separate
> (sequential or index) scan of the deficiencies table. The only way I can
> see to do
Now that I've given your problem more thoughts (and searched for similar
stuff),
I think what you need is generating a cross table resp. pivot table.
Related to this, I am thinking of a query using Conditional Expressions
like
COUNT ( CASE WHEN ... THEN 1 ELSE NULL) in order to use GROUP BY.
Toget
