List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:May 29 2006 5:55pm
Subject:Re: ugly SQL for a report...
View as plain text  
On 5/29/06, Cory Robin wrote:
>
> SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number,
> pnr.created_by,
>         GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last
> DESC SEPARATOR '<br>') names,
>         (SELECT SUM(pf.base_fare*(SELECT COUNT(1) FROM pnr_passengers pp
> WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares pf WHERE pf.ID_pnr=pnr.ID )
> base_fare,
>         (SELECT SUM(pf.tax*(SELECT COUNT(1) FROM pnr_passengers pp WHERE
> pp.ID_pnr=pnr.ID )) FROM pnr_fares pf WHERE pf.ID_pnr=pnr.ID ) tax ,
>         (SELECT SUM(psf.segfee_amount*(SELECT COUNT(1) FROM
> pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_segments_fees psf
> INNER JOIN pnr_segments pss ON psf.ID_segments=pss.ID WHERE
> psf.segfee_code='XF' AND pss.ID_pnr=pnr.ID ) XF ,
>         (SELECT SUM(amount_value) service_fees FROM pnr_service_fees
> WHERE ID_pnr=pnr.ID) services ,
>         (SELECT SUM(amount) FROM pnr_payments_credits WHERE
> ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='sale') payments,
>         (SELECT SUM(amount) FROM pnr_payments_credits WHERE
> ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='credit') credits
>     FROM pnr INNER JOIN pnr_passengers pp
>         ON pnr.ID=pp.ID_pnr
>     WHERE pnr.reservationdatetime>='2000-05-29 00:00:00'
>     AND pnr.reservationdatetime<='2006-05-29 23:59:59'
> GROUP BY pnr.ID
> ORDER BY reservationdatetime;

One way to avoid the repeated COUNT might be to use a nested FROM that
pre-calculates the count as a pseudo-column:
SELECT
	pnr_precalc.ID ID_pnr,
	pnr_precalc.reservationdatetime,
	pnr_precalc.conf_number,
	pnr_precalc.created_by,
	GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last DESC
SEPARATOR '<br>') names,
	(SELECT SUM(pf.base_fare * pnr_precalc.passengercount) FROM pnr_fares
pf WHERE pf.ID_pnr=pnr_precalc.ID ) base_fare,
	(SELECT SUM(pf.tax*pnr_precalc.passengercount) FROM pnr_fares pf
WHERE pf.ID_pnr=pnr_precalc.ID ) tax ,
	(
		SELECT SUM(psf.segfee_amount*pnr_precalc.passengercount)
		FROM pnr_segments_fees psf INNER JOIN pnr_segments pss ON
psf.ID_segments=pss.ID
		WHERE psf.segfee_code='XF' AND pss.ID_pnr=pnr_precalc.ID
	) XF,
	(SELECT SUM(amount_value) service_fees FROM pnr_service_fees WHERE
ID_pnr=pnr_precalc.ID) services ,
	(SELECT SUM(amount) FROM pnr_payments_credits WHERE
ID_pnr=pnr_precalc.ID AND bln_payment='1' AND transaction_type='sale')
payments,
	(SELECT SUM(amount) FROM pnr_payments_credits WHERE
ID_pnr=pnr_precalc.ID AND bln_payment='1' AND
transaction_type='credit') credits

FROM
	(
		SELECT
			pnr.ID ID_pnr,
			pnr.reservationdatetime,
			pnr.conf_number,
			pnr.created_by,
			(SELECT COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )
passengercount
		FROM
				pnr
	) pnr_precalc
		INNER JOIN pnr_passengers
	pp
		ON pnr.ID=pp.ID_pnr
WHERE
	pnr_precalc.reservationdatetime >= '2000-05-29 00:00:00'
		AND
	pnr_precalc.reservationdatetime <= '2006-05-29 23:59:59'
GROUP BY
	pnr_precalc.ID
ORDER BY
	reservationdatetime;
(You probably have to fix this since I can not test it.)


If you see too many loops over pnr_service_fees and
pnr_payments_credits in your explain output you can rework them in the
same way. If this doesn't help, we are going to need a lot more from
the schema and the explain output.

Jochem
Thread
ugly SQL for a report...Cory29 May
  • Re: ugly SQL for a report...Jochem van Dieten29 May
  • Re: ugly SQL for a report...Cory Robin29 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
      • Re: ugly SQL for a report...Cory Robin30 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
    • Re: ugly SQL for a report...Peter Brawley30 May