CODEWITHSUNDEEP
mysql
Absolute86
Absolute86

Reputation: 13

MySQL Sum column IF ID is in another table query

Edit ---

I thought I had it, but the below doesn't seem to work. It looks like it's totaling everything from the DB.

SELECT SUM( drivetime ) AS drivetime, record_id
FROM  `workforce` 
WHERE EXISTS (    
SELECT *
FROM project_info
WHERE date1
BETWEEN  'xx-xx-xxxx'
AND  'xx-xx-xxxx'
)

Will try below suggestions.

-- end edit

The end goal is to (pseudo) : SELECT SUM(work) from tasks where R_ID is in (select ID from Info where date between X and X)

Info

+----------------------+
| ID  | other fields...|
+-----+----------------+
|  1  | etc...         |
+-----+----------------+
|  2  | etc...         |
+-----+----------------+
|  3  | etc...         |
+-----+----------------+

Tasks

+----------------------+
| R_ID  | work| other..|
+-------+-----+--------+
|  1    | 10  |        |
+-------+-----+--------+
|  2    | 2   |        |
+-------+-----+--------+
|  2    | 2   |        |
+-------+-----+--------+
|  3    | 2   |        |
+-------+-----+--------+

Our second query returns that 2 & 3 are within the date range. So the sum would be 6. What is the best way to accomplish this?

Everything I've been able to find has hard conditions - not range conditions based on a second select. I currently get my range from an initial select and then iterate over all records in a given table to sum what's within the range. This is terribly slow over thousands of records.

I'll keep looking and post back anything I find / try.

Thanks for reading!

-- Current code (edited to remove extra fields & data) for posterity. Only tagged mySQL as server side is irrelevant (and is being converted over to PHP). 

psql = "SELECT * FROM project_info where approved='yes' where date1 BETWEEN '"& sdate &"' AND '" & edate & "' order by id DESC"
Set prs = Conn.Execute(psql)

do until prs.EOF

sumsql = "SELECT SUM(drivetime) as drivetime from workforce where approved='yes' and report_id='" & prs("ID") & "'"
set sumrs = Conn.Execute(sumsql)

dtime = dtime + cLng(sumrs("drivetime"))

prs.movenext
loop

Upvotes: 0

Views: 1562

Answers (1)

Arth
Arth

Reputation: 13110

I think this is a fairly simple join, sum and group by:

  SELECT t.r_id, SUM(t.work) work_sum
    FROM tasks t
    JOIN info i
      ON i.id = t.r_id
     AND i.date BETWEEN xxx AND yyy
GROUP BY t.r_id

If you want a null work_sum for tasks with no info in that range:

   SELECT t.r_id, SUM(t.work) work_sum
     FROM tasks t
LEFT JOIN info i
       ON i.id = t.r_id
      AND i.date BETWEEN xxx AND yyy
 GROUP BY t.r_id

If you want 0 work_sum for tasks with no info in that range:

   SELECT t.r_id, COALESCE(SUM(t.work),0) work_sum
     FROM tasks t
LEFT JOIN info i
       ON i.id = t.r_id
      AND i.date BETWEEN xxx AND yyy
 GROUP BY t.r_id

Upvotes: 1

Related Questions