Optimising Script

[esport]

Hi Guys,

I have a query with a sub query in it. I runs extremely slow and i was wondering if there is an alternative way to optimise it.

I want to count the number sold between 2 dates and grouped by state.

 

Here is what I currently have

SELECT erg.Manufacturer
   (SELECT SUM(NumberSold) as num_sold FROM erg_data erg_vic WHERE erg_vic.DeliveryState = 'VIC' AND erg_vic.Manufacturer = erg.Manufacturer AND (DeliveryMonth='12' AND DeliveryYear='2010')) AS num_sales_vic ,
(SELECT SUM(NumberSold) as num_sold FROM erg_data erg erg_nsw WHERE erg_nsw.DeliveryState = 'NSW' AND erg_nsw.Manufacturer = erg.Manufacturer AND (DeliveryMonth='12' AND DeliveryYear='2010')) AS num_sales_nsw

FROM erg_data erg WHERE (DeliveryMonth='12' AND DeliveryYear='2010') GROUP BY erg.Manufacturer 

 

All information is from the 1 table.

How do i count and group at the same time without the sub queries.

Hope this makes sense.

Thanks in advance

 

Daniel

 

[requinix]

If you need a row with the manufacturer, VIC, and NSW values, not really.

If you want the data then select the manufacturer and version, limit the version to VIC and NSW (and the delivery date), and group by the two fields.