Multiply output of subquery (MySQL)-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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