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

Multiply output of subquery (MySQL)

I’m trying to multiply the result of a subquery with a field from the ‘main’ query. See the following example:

Table: subscriptions

  • id
  • title
  • price

Table: users

  • subscription_id
SELECT 
    subscriptions.id,
    subscriptions.title,
    (select count(*) from users where users.subscription_id = subscriptions.id) AS qty
    SUM(qty * subscriptions.price) AS total
FROM subscriptions

This gives the error Unknown column 'qty' in 'field list'. So it seems like the result from the subquery isn’t available in the SELECT field. After searching StackOverflow I found some of the same questions and it seems I need to move the subquery from the select to a JOIN. This seems simple enough but I’m having trouble to modify my own query to work like this. Anyone who can push me in the right direction?

Tags:

3

Answers


  1. 0

    Don’t put the subquery in the SELECT list, join with it.

    SELECT s.id, s.title, u.qty, s.price * u.qty AS total
FROM subscriptions AS s
JOIN (
    SELECT subscription_id, COUNT(*) AS qty
    FROM users
    GROUP BY subscription_id
) AS u ON s.id = u.subscription_id
    Login or Signup to reply.
  2. 0

    Almost right.

    SELECT 
    s.id,
    s.title,
    SUM(s.price * (select count(*) from users u where u.subscription_id = s.id)) AS total
FROM subscriptions s
GROUP BY s.id, s.title
    Login or Signup to reply.
  3. 0

    I tried to reslove your query, check it

    https://dbfiddle.uk/xrMrT7Y4

    I don’t know why someone has deleted my answer. Here I found issue in your query is you didn’t group the aggregate function & If you are comparing ID then both tables should be considered. @Vinze

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

Back To Top
Search