John Siniger John Siniger - 5 months ago 7
SQL Question

MySQL Select second count of pre selected results

Hello I am trying to make a Select where the uses chooses department and would like to have clause

WHERE
this department is first, let's say we select 10 results from department: Taxes and then make a
SUM
SELECT
of fee
WHERE status = 1
. Which results be selected based on the first select All the results are coming from the same table.

| id | department | status | fee |
----------------------------------
| 1 | tax | 1 | 20 |
| 2 | tax | 2 | 20 |
| 3 | tax | 1 | 20 |
| 4 | accounting | 1 | 20 |


So I would like to select if department is choose as tax, and status is 1 the sum of FEE columns which should be 40

So far my Select query looks like this:

SELECT P.id, P.fee, (SELECT SUM(P.fee) FROM cases P WHERE status = 1) as fee_USD
FROM cases P WHERE 1";
if (!empty($department)) { $sql .= " AND P.department = '$department'"; }


the last line is checking if department is given as select option. there are other options as well but to make it simple I have pasted only this part of it. Any help is welcome.

In the Current Selection Fee is = 80

Answer

You have to add correlation to your query:

SELECT P1.id, P1.fee, 
      (SELECT SUM(P2.fee) 
       FROM cases P2 
       WHERE P2.department = P1.department AND status = 1) as fee_USD
FROM cases P1 
WHERE 1 ... 

This way the subquery will return the SUM of only those records which are related to the current record of the main query.

Comments