[SOLVED] MySQL Syntax for Multi Select

[random1]

I'm currently developing my reports section of my site and currently have the following as one statement.

 

SELECT
COUNT(`product_id`) as `total_active_products`,            < ---- This should use the field `product_status_id` == 1 as WHERE
COUNT(`product_id`) AS `total_inactive_products`,         < ---- This should use the field `product_status_id` == 0 as WHERE
COUNT(`product_id`) AS `total_products`,
AVG(`product_price`) AS `average_product_price`
FROM `product`;

 

How do I do this as one statement but have WHERE clauses?

[Mchl]

Using subqueries

 

SELECT
(SELECT COUNT(`product_id`) FROM `product` WHERE `product_status_id` == 1) AS `total_active_products`,
(SELECT COUNT(`product_id`) FROM `product` WHERE `product_status_id` == 0) AS `total_inactive_products`,
...

 

Be aware though, that too many subqueries and your query will get sloooooow...

 

Do I have to mention that `total_inactive_products` = `total_products` - `total_active_products`? That's one subquery less, and some time saved.

 

[random1]

Thanks

 

I ended up with:

 

SELECT DISTINCT
(SELECT COUNT(`product_id`) FROM `product` WHERE `product_status_id` = 0) AS `total_inactive_products`,
(SELECT COUNT(`product_id`) FROM `product` WHERE `product_status_id` = 1) AS `total_active_products`,
(SELECT COUNT(`product_id`) FROM `product` WHERE `product_status_id` = 2) AS `total_deleted_products`,
(SELECT COUNT(`product_id`) FROM `product`) AS `total_products`,
(SELECT MIN(`product_price`) FROM `product`) AS `min_product_price`,
(SELECT MAX(`product_price`) FROM `product`) AS `max_product_price`,
(SELECT AVG(`product_price`) FROM `product`) AS `average_product_price`
FROM `product`;

 

I'll keep an eye on execution time and also add it to an SQL transaction.