CODEWITHSUNDEEP
mysqlsql
Othman
Othman

Reputation: 3018

temporary table has a count for each other table

I'm trying to make a statistics page in my php script. in order to select the count from each table I need more than 30 Queries like this 

SELECT COUNT(order_id) as `uncompleted_orders` FROM `orders` WHERE `order_status` != 0

and then I need to run another query like this: 

SELECT COUNT(order_id) as `completed_orders` FROM `orders` WHERE `order_status` = 1

I've tried this approach, but it didn't work:

SELECT COUNT(order_id) as `uncompleted_orders` FROM `sd_orders` WHERE `order_status` != 4;
SELECT COUNT(order_id) as `completed_orders` FROM `sd_orders` WHERE `order_status` = 4;

Is there any way to creat a new temp table in MySQL contains the count for other tables?

Upvotes: 0

Views: 88

Answers (2)

MatBailie
MatBailie

Reputation: 86765

Without more information it's impossible to generalise, but there are many constructs that can help you here.


First, your example is actually from one table, and not two. This means that you can do the following...

SELECT
  COUNT(CASE WHEN order_status =  4 THEN order_id END) AS   complete_orders,
  COUNT(CASE WHEN order_status <> 4 THEN order_id END) AS incomplete_orders
FROM
  sd_orders

This works because COUNT(<something>) doesn't include an NULLs in the results. And by not including an ELSE clause, anything that doesn't match returns NULL. Another way people accomplish the same result is SUM(CASE WHEN ? THEN 1 ELSE 0 END).


Second, where you do actually have multiple tables, you can combine the results in several different ways...

-- Where you want one value from each table...
--------------------------------------------------------------------------------
SELECT
  (SELECT COUNT(*) FROM table1 WHERE fieldx = ?)   AS value1,
  (SELECT COUNT(*) FROM table2 WHERE fieldy = ?)   AS value2



-- Where you want one row of values from each table...
--------------------------------------------------------------------------------
SELECT
  table1_summary.value1 AS table1_value1,
  table1_summary.value2 AS table1_value2,
  table2_summary.value1 AS table2_value1,
  table2_summary.value2 AS table2_value2
FROM
(
  SELECT
    COUNT(CASE WHEN fieldx  = ? THEN id END) AS value1,
    COUNT(CASE WHEN fieldx <> ? THEN id END) AS value2
  FROM
    table1
)
  AS table1_summary
CROSS JOIN
(
  SELECT
    COUNT(CASE WHEN fieldy  = ? THEN id END) AS value1,
    COUNT(CASE WHEN fieldy <> ? THEN id END) AS value2
  FROM
    table2
)
  AS table2_summary



-- Where you want many rows, but of the same fields, from each table...
--------------------------------------------------------------------------------
SELECT
  *
FROM
(
  SELECT
    'Table1'                                 AS source_table,
    fielda                                   AS some_grouping,
    COUNT(CASE WHEN fieldx  = ? THEN id END) AS value1,
    COUNT(CASE WHEN fieldx <> ? THEN id END) AS value2
  FROM
    table1
  GROUP BY
    fielda

  UNION ALL

  SELECT
    'Table2'                                 AS source_table,
    fieldb                                   AS some_grouping,
    COUNT(CASE WHEN fieldy  = ? THEN id END) AS value1,
    COUNT(CASE WHEN fieldy <> ? THEN id END) AS value2
  FROM
    table2
  GROUP BY
    fieldb
)
  AS summary
ORDER BY
  source_table,
  some_grouping,
  value1,
  value2


As you can see, there are a lot of ways to do this. How you approach it totally depends on your data and your needs.

Upvotes: 0

Stelian Matei
Stelian Matei

Reputation: 11623

You could try something like this:

SELECT 
(
   SELECT COUNT(order_id) FROM `sd_orders` WHERE `order_status` != 4
)  as `uncompleted_orders`,
(
   SELECT COUNT(order_id) FROM `sd_orders` WHERE `order_status` = 4
)  as `completed_orders`

You will have a result set with one row and a field for each count.

Upvotes: 4

Related Questions