Make SQL query more efficient
- by Webnet
I currently have this query which runs 2 of the exact same sub queries but pull different data. When I make the values comma separated it throws an SQL error saying the sub query can return only one value. Is there anything else I can do to avoid running multiple sub queries?
SELECT
product_id,
(
SELECT
COUNT(listing_id)
FROM ebay_archive_product_listing_assoc
WHERE product_id = product_master.product_id) as listing_count,
sku,
type_id,
(
SELECT
AVG(ebay_archive_listing.current_price),
AVG(ebay_archive_listing.buy_it_now_price)
FROM ebay_archive_listing
WHERE id IN (
SELECT listing_id FROM ebay_archive_product_listing_assoc WHERE product_id = product_master.product_id
) AND
ebay_archive_listing.start_time >= '.$startTimestamp.' AND
ebay_archive_listing.start_time <= '.$endTimestamp.' AND
ebay_archive_listing.current_price > 0
) as average_bid_price,
(
SELECT
FROM ebay_archive_listing
WHERE id IN (
SELECT listing_id FROM ebay_archive_product_listing_assoc WHERE product_id = product_master.product_id
) AND
ebay_archive_listing.start_time >= '.$startTimestamp.' AND
ebay_archive_listing.start_time <= '.$endTimestamp.' AND
ebay_archive_listing.buy_it_now_price > 0
) as average_buyout_price
FROM product_master
I'm aware of the syntax error... I'm selecting 2 seperate averages and am wondering if I can do it any simpler way.
