How to join a sum()'s result with a select query

I've got a basic transactions table with 4 total columns as follows :

`transaction_id` int(11) NOT NULL AUTO_INCREMENT,
`fruit_id` int(11),
`person_id` int(11),
`quantity_bought` int(11),

example select * query :

transaction_id	fruit_id	person_id	quantity_bought
1	banana	alex	65
2	banana	joe	25
3	banana	jenny	70
4	apple	dan	80
4	apple	danny	50

i'm required to add the total_quantity_bought of each fruit to every row of this select query :

SELECT transactions.* FROM transactions;

here's an example of the desired output :

transaction_id	fruit_id	person_id	quantity_bought	total_quantity_bought
1	banana	alex	65	160
2	banana	joe	25	160
3	banana	jenny	70	160
4	apple	dan	80	130
4	apple	danny	50	130

this is what i tried so far and miserably failed :

select x.* , y.total_quantity_bought
from
   (SELECT *
    FROM transactions) x
    cross join
   (SELECT fruit_id , SUM(quantity_bought) AS total_quantity_bought
    FROM transactions
    GROUP BY fruit_id) y

i thought of creating a View for sums like so, but i'm looking for a solution that doesnt require a View

fruit_id	total_quantity_bought
banana	160
apple	130

any help appreciated.

Solution

You are basically there. Your JOIN wants to specify that for everything on the left (from the transactions table) should join to one associated total from the right (the totals subquery) ... and that join should be conditional on a matching fruit_id.

The performance of this query may drag (severely) as the dataset grows. You might want to look at either caching the totals (using a database trigger etc) or rendering the totals in code elsewhere.

If you go for the latter (rendering it elsewhere), you might be interested in the WITH ROLLUP function:

https://mariadb.com/kb/en/select-with-rollup/

SELECT
   transactions.*,
   totals.total_quantity_bought
FROM
   transactions   -- These are the individual transactions

   INNER JOIN     -- Join each individual transaction against the calculated totals

   (
    SELECT
       fruit_id,
       SUM(quantity_bought) AS total_quantity_bought
    FROM transactions
    GROUP BY fruit_id
   ) as totals    -- These are the calculated totals (subqueried)

   ON totals.fruit_id = transactions.fruit_id
                  -- This joins the individual records to the totals by `fruit_id`

(Demo: http://sqlfiddle.com/#!9/74ae7e/10 )