Question 20490 in Mysql Question posted in
The official MySQL documentation can be found here.

Get sum of column below the column in sql – Mysql

I have the following query:

select 
   product_name, 
   (select bid_price 
    from bids 
    where bid_id = current_bid_id) as final_price 
from items 
where close_date > '2023/01/01' 
  and close_date < '2023/02/01';

which outputs:

product_name final_price
  ball          20
  bat           30
hockey_stick    50

Instead of this, I want either:

product_name final_price
  ball          20
  bat           30
hockey_stick    50
  total         100

or

product_name final_price total
  ball          20        100
  bat           30        100
hockey_stick    50        100

I’m struggling on how to do this as its an alias column

Tags:

3

Answers


  1. 0

    Try this one:

    SELECT 
i.product_name, 
(SELECT bid_price FROM bids WHERE bid_id = i.current_bid_id) AS final_price,
COUNT(*) AS total_bids,
SUM((SELECT bid_price FROM bids WHERE bid_id = i.current_bid_id)) AS total_price FROM items AS i  INNER JOIN bids AS b ON i.current_bid_id = b.bid_id WHERE i.close_date > '2023-01-01' AND i.close_date < '2023-02-01' GROUP BY i.product_name;
    Login or Signup to reply.
  2. 0

    You can use: GROUP BY WITH ROLLUP

    select 
    case when product_name is null then 'TOTAL' else product_name END as product_name,  
    sum(final_price)
from items 
group by product_name with rollup

    see: DBFIDDLE

    results:

    product_name sum(final_price)
    ball 20
    bat 30
    hockey_stick 50
    TOTAL 100
    Login or Signup to reply.
  3. 0

    As a starter: presumably the price selection subquery in the select clause always returns at most one row (otherwise you query would error), so it could be expressed as a left join (or even as an inner join, if there always is a bid to match the item).

    Then: we can add easily add another column to the resultset that computes the total of all prices using window functions:

    select i.product_name, 
    b.bid_price as final_price,
    sum(b.bid_price) over() as total_price
from items i
left join bids b on b.bid_id = i.current_bid_id
where i.close_date >= '2023-01-01' and i. close_date < '2023-02-01';

    I slightly changed the date filtering, assuming that close_date is of a date-like datatype and that you want to filter on the entire day.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search