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 :

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 :

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

any help appreciated.

CodePudding user response：

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 )

CodePudding user response：

Possibly I just got beat to the answer, but here's my attempt

SELECT t.transaction_id, t.fruit_id, t.person_id, t.quantity_bought, grouped_fruit.total_quantity_bought
FROM transactions t
JOIN (
    SELECT fruit_id, SUM(quantity_bought) total_quantity_bought
    FROM transactions
    GROUP BY fruit_id
) grouped_fruit ON t.fruit_id = grouped_fruit.fruit_id