Mysql – How to send two query into one? Is there any other alternative

MySQL

So I have this query that gets me the number of failed parts in the order. for example 94 failed. However, I want to see the total amount of parts in that order. For example, 94 failed and 200 total. How can I achieve that in one query? If it's not possible how can I combine two queries in one result table?

SELECT status, COUNT(order)
FROM result
WHERE status='failed' and order = '4373'
GROUP BY status

Best Answer

For MySQL (as the question tagged):

SELECT SUM(status='failed') AS failed_orders, COUNT(1) AS total_orders
FROM result
WHERE order = '4373'
-- GROUP BY status -- ??

For another DBMS (for example, for Microsoft SQL Server):

SELECT COUNT(CASE WHEN status='failed' THEN 1 END) AS failed_orders, COUNT(1) AS total_orders
FROM result
WHERE order = '4373'

Related Solutions

Mysql – Use order from another select clause

Unless I'm grossly misunderstanding your structure, a simple join should work fine:

SELECT stories.vid, body, timestamp, COUNT(votes.id) votecnt FROM stories
INNER JOIN votes ON stories.vid=votes.vid
WHERE stories.lv=1 AND stories.status=1
GROUP BY votes.vid
ORDER BY votecnt DESC

I'm not sure what you mean by

It would be even better, if the first query would sort the table by status = 1 count at first, and then total votes.

since your where statement only uses votes/stories that have status=1.

Mysql – selecting the month day and year from a transaction table

You will want to use the DATE_FORMAT function in MySQL to format the date for each query.

-- query 1
select date_format(date, '%m-%d') `m-d`,
  sum(amount) Total
from mytable
group by date_format(date, '%m-%d');

See SQL Fiddle with Demo. Gives a result:

|   M-D | TOTAL |
-----------------
| 04-08 |    50 |
| 05-01 |   200 |
| 05-03 |   400 |

-- query 2
select date_format(date, '%Y-%m') `Y-m`,
  sum(amount) Total
from mytable
group by date_format(date, '%Y-%m');

See SQL Fiddle with Demo. Gives the result:

|     Y-M | TOTAL |
-------------------
| 2013-04 |    50 |
| 2013-05 |   600 |

Best Answer

Related Solutions

Mysql – Use order from another select clause

Mysql – selecting the month day and year from a transaction table

Related Question