Can this query be made simpler?

[fastsol]

I have this query that fully works the way it is, but I am wondering if I am overdoing some things in it.  I know this may be hard to tell without knowing the full relationship between all the tables in the query.  I'm basically running 3 subqueries mostly based on a timestamp value and other specific clauses per subquery.  Where the main query is only gathering based on the timestamp.

 

Like I said it works perfect from what I can verify by cross referencing the tables manually and checking the results the query returned.  I just want to know if there is a better way to do all this.

SELECT `products`.`id` AS `pid`, `products`.`prod_name`,
 
(SELECT COUNT(*) FROM `products` 
INNER JOIN `quote_deposits` ON `quote_deposits`.`product_id` = `products`.`id`
INNER JOIN `quote_responses` ON `quote_responses`.`id` = `quote_deposits`.`q_id`
WHERE `quote_responses`.`purchased` = 0 AND `quote_deposits`.`dep_date` >= $committed_start
) AS `committed`, 

(SELECT COUNT(`products`.`id`) FROM `products` 
  INNER JOIN `quote_deposits` ON `quote_deposits`.`product_id` = `products`.`id`
  INNER JOIN `quote_responses` ON `quote_responses`.`id` = `quote_deposits`.`q_id`
  WHERE `quote_responses`.`purchased` = 1 AND `products`.`id` = `pid` AND `quote_deposits`.`dep_date` >= $committed_start
) AS `total_per_item`,

(SELECT COUNT(`products`.`id`) FROM `products` 
  INNER JOIN `quote_deposits` ON `quote_deposits`.`product_id` = `products`.`id`
  INNER JOIN `quote_responses` ON `quote_responses`.`id` = `quote_deposits`.`q_id`
  INNER JOIN `schedule` ON `schedule`.`deposit_id` = `quote_deposits`.`id`
  WHERE `quote_responses`.`purchased` = 0 AND `schedule`.`cancelled` != '' AND `products`.`id` = `pid` AND `quote_deposits`.`dep_date` >= $committed_start
) AS `total_cancelled`

FROM `products`
INNER JOIN `quote_deposits` ON `quote_deposits`.`product_id` = `products`.`id`
WHERE `quote_deposits`.`dep_date` >= $committed_start
GROUP BY `products`.`prod_name` ORDER BY `products`.`prod_name` ASC

[fastsol]

Oops, forgot one thing in the query when I was making it more readable for the forum.  Here is the updated version.

SELECT `products`.`id` AS `pid`, `products`.`prod_name`,
 
(SELECT COUNT(*) FROM `products` 
INNER JOIN `quote_deposits` ON `quote_deposits`.`product_id` = `products`.`id`
INNER JOIN `quote_responses` ON `quote_responses`.`id` = `quote_deposits`.`q_id`
WHERE `quote_responses`.`purchased` = 0 AND `products`.`id` = `pid` AND `quote_deposits`.`dep_date` >= $committed_start
) AS `committed`, 

(SELECT COUNT(`products`.`id`) FROM `products` 
  INNER JOIN `quote_deposits` ON `quote_deposits`.`product_id` = `products`.`id`
  INNER JOIN `quote_responses` ON `quote_responses`.`id` = `quote_deposits`.`q_id`
  WHERE `quote_responses`.`purchased` = 1 AND `products`.`id` = `pid` AND `quote_deposits`.`dep_date` >= $committed_start
) AS `total_per_item`,

(SELECT COUNT(`products`.`id`) FROM `products` 
  INNER JOIN `quote_deposits` ON `quote_deposits`.`product_id` = `products`.`id`
  INNER JOIN `quote_responses` ON `quote_responses`.`id` = `quote_deposits`.`q_id`
  INNER JOIN `schedule` ON `schedule`.`deposit_id` = `quote_deposits`.`id`
  WHERE `quote_responses`.`purchased` = 0 AND `schedule`.`cancelled` != '' AND `products`.`id` = `pid` AND `quote_deposits`.`dep_date` >= $committed_start
) AS `total_cancelled`

FROM `products`
INNER JOIN `quote_deposits` ON `quote_deposits`.`product_id` = `products`.`id`
WHERE `quote_deposits`.`dep_date` >= $committed_start
GROUP BY `products`.`prod_name` ORDER BY `products`.`prod_name` ASC

[kicken]

I'm thinking maybe something like this:

SELECT 
	`products`.`id` AS `pid`
	, `products`.`prod_name`,
	, SUM(CASE WHEN quote_responses.purchased = 0 THEN 1 ELSE 0 END) as committeed
	, SUM(CASE WHEN quote_responses.purchased = 1 THEN 1 ELSE 0 END) as total_per_item
	, SUM(CASE WHEN quote_responses.purchased = 0 AND schedule.cancelled != '' THEN 1 ELSE 0 END) as total_cancelled
FROM `products`
INNER JOIN `quote_deposits` ON `quote_deposits`.`product_id` = `products`.`id`
LEFT JOIN `quote_responses` ON `quote_responses`.`id` = `quote_deposits`.`q_id`
LEFT JOIN `schedule` ON `schedule`.`deposit_id` = `quote_deposits`.`id`
WHERE 
	`quote_deposits`.`dep_date` >= $committed_start
GROUP BY 
	`products`.`id` AS `pid`
	, `products`.`prod_name` 
ORDER BY 
	`products`.`prod_name` ASC

As you said though, hard to know for sure without being more familiar with the tables and the data within them. Edited July 23, 2014 by kicken

[fastsol]

That was awesome, thank you.  There were a couple things it didn't like when I first ran it but I figured them out.  There was an extra , before the first SUM and the GROUP BY didn't like the `products`.`id` AS `pid` being declared again, so it worked with just `pid` instead.

 

I checked results before and after running on a couple dates to make sure the result was the same and it was!

[Jacques1]

You two definitely need to get rid of this strange habit of enclosing all identifiers in backticks. It's just ugly, unnecessary and can lead to hard-to-find bugs. For example, if you accidentally put the dot of a qualified name between the backticks (e. g., `foo.bar`), that's still a formally valid identifier but of course not what you meant. Even worse, the error message will look like the column foo.bar is missing, so you'll scratch your head until you visually inspect the query and spot the messed-up identifier. I've seen people spend hours on this.

 

The backticks only exist for one reason: To allow the use of non-standard identifiers with strange symbols or reserved words. Since you should never use non-standard identifiers, there's never a need for backticks. So just get rid of them.