0

In my DB I have multiple activities that are being logged, and during those activities the number of shots taken at targets are logged. There are different types of targets so I'm using a link table to map the types of targets to the activities and in the link table registering the number of hits. I have two other tables where one defines the types of gear available for use on targets and another table that links the gear used to the activity that was logged. My goal is to get the overall hit accuracy (which I'm able to figure out) as well as a break down of the hit accuracy by gear (which for the life of me I can't figure out the query for).

Here is the table setup (note the target_type table isn't shown since I don't need it in the query):

Edit 1 - Add SQL File: SQL Export File

activities
+-------------+-------------+---------+
| activity_id | shots_taken | user_id |
+-------------+-------------+---------+
| 1534946127  | 13          | 1234    |
| 1535548314  | 15          | 1234    |
| 1535985068  | 5           | 1234    |
+-------------+-------------+---------+

target_map
+-------------+-------------+-------------+
| activity_id | hits        | target_type |
+-------------+-------------+-------------+
| 1534946127  | 2           | 2           |
| 1534946127  | 3           | 10          |
| 1535985068  | 2           | 102         |
| 1535985068  | 3           | 101         |
+-------------+-------------+-------------+

gear_map
+-------------+-------------+------------+
| gear_map_id | gear_id     | activity_id|
+-------------+-------------+------------+
| 1           | 2           | 1535985068 |
| 2           | 3           | 1535985068 |
+-------------+-------------+------------+

gear
+-------------+-------------+------------+
| gear_id     | gear_type   | user_id    |
+-------------+-------------+------------+
| 2           | lazer       | 1234       |
| 3           | slingshot   | 1234       |
+-------------+-------------+------------+

Here is my query to get the overall hit accuracy:

SELECT round(((
    SELECT sum(target_map.hits) 
    FROM target_map 
    LEFT JOIN activities on activities.activity_id = target_map.activity_id 
    WHERE activities.user_id = 1234)/(
    SELECT sum(activities.shots_taken) 
    FROM activities 
    WHERE activities.user_id = 1234) * 100 ),2) AS percentage

I'm not that concerned with having the query return the percentages (I can calculate it afterwards) as I am accurate sums. When I try and get the sums across these tables using JOINS the numbers returned are inflated.

Edit 2: This is the previous query I have tried but instead of getting a total of 5 hits out of 13 shots, I get 10 hits out of 13 shots.

SELECT gear_map.gear_id, gear.gear_type, sum(target_map.hits) as total_hits, activities.shots_taken 
from gear_map inner 
join activities on gear_map.activity_id = activities.activity_id 
inner join target_map on target_map.activity_id = activities.activity_id 
inner join gear on gear_map.gear_id = gear.gear_id

Edit 3

I've tried a new query based on @Barmar's suggestion but I'm missing something, since the numbers are still inflated.

SELECT *
FROM gear_map AS gear_map
LEFT JOIN (
    SELECT target_map.activity_id, SUM(target_map.hits) AS amount
    FROM target_map
    ) AS target_map 
ON target_map.activity_id = gear_map.activity_id
LEFT JOIN (
    SELECT activities.activity_id, SUM(activities.shots_taken) AS total_shots
    FROM activities
    ) AS activities 
ON activities.activity_id = gear_map.activity_id
GROUP BY gear_id

Can someone please nudge me in the right direction? Thanks.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Jeff
  • 195
  • 9
  • Please see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Oct 05 '18 at 15:56
  • 1
    When you use `LEFT JOIN` you shouldn't refer to the joined table in the `WHERE` clause, since that will filter out all the non-matching rows. If you don't want the non-matching rows you shouldn't use `LEFT JOIN` in the first place, use `INNER JOIN`. – Barmar Oct 05 '18 at 16:04
  • You should show the query that's getting the inflated numbers. My guess is your problem is answer [here](https://stackoverflow.com/questions/37978511/join-tables-with-sum-issue-in-mysql/37979049#37979049) – Barmar Oct 05 '18 at 16:08
  • I've just uploaded the SQL file for the database structure while I go back and try to remember the statement that got me closest (but still incorrect) results. @Barmar I'm also looking at your suggestion now too. – Jeff Oct 05 '18 at 17:11
  • I've tried the query in Edit 3 but I'm still not getting the correct results. What am I missing (besides not working with sql enough)? (edited comment to remove query because it was hard to read). – Jeff Oct 05 '18 at 19:17

0 Answers0