Query:
SELECT business.bussId,
(select count(invoices.userId) from invoice where invoice.userId = '3000' )
as invoiceCount,
(select SUM(invoices.price) from invoice where invoice.userId = '3000' )
as invoiceprice ,
FROM business WHERE business.bussId=100
How could I get invoice price and invoiceCount using one nested select ?
Move the subquery to the from clause:
SELECT b.bussId, i.invoiceCount, i.invoiceprice
FROM business b cross join
(select count(i.userId) as invoiceCount, SUM(i.price) as invoiceprice
from invoice i
where i.userId = '3000'
) i
WHERE b.bussId = 100;
You can actually write this without the subquery, but your question is specifically about using subqueries.
That form would be:
SELECT b.bussId, count(i.userId) as invoiceCount, SUM(i.price) as invoiceprice
FROM business b left join
invoice i
on i.userId = '3000' and b.bussId = 100
GROUP BY b.bussId;
cross join creates a cartesian product between the two tables. When one of the tables has just one row (as is presumably true in this case), it just adds the extra columns to the result set.