> This query works fine but I need to select about 10-15 more
> columns from the
> Master (m) table. The only way I can seem to get that to work is if I also
> group on all the columns but this totally kills the query speed and Im not
> sure I am even getting correct results in that case.
>
> Basically this selects 2 columns from the Master table and SUM's the
> QtyShipped from the Detail table for each record in the master. How can I
> select more columns from Master without grouping?
>
> SELECT m.po,m.statcomm,sum(d.qtyshipped) AS TotalQty
> FROM pos m,posd d
> WHERE m.shipdate BETWEEN '02/25/2001' AND '02/25/2001'
> AND m.po = d.po
> GROUP BY m.po,m.statcomm
> ORDER BY m.po
The problem with aggrigate queries is that all columns that you want out
either have to be grouped or be via an aggrigate function - this gets
unfeasably slow on large numbers of columns
Have you tried indexing the columns that you want out? That usually speeds
up aggrigate functions some
Also, using HAVING instead of WHERE may improve performance a little
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
