Hey i have three distinct sql queries which need to be merged.(shown here are just two) I tried to put one query in the FROM Clause in parenthesis example given I am trying to list user with last date of his order and in the same time i want to list the ammount of order, it would work but because of the GROUPY BY i get an error message --> "subquery must return only one column" and UNION does not work because of "each UNION query must have the same number of columns" So the problem is simple: I just want to merge to results from two queries togheter in one table

SELECT DISTINCT order.ordner_nr,name,oDate,             

(
SELECT  
   order_nr, COUNT(*) as ammountOrders
FROM
     order
GROUP BY    -- Error occurs 
   order_nr
)



FROM user
INNER JOIN order
using (order_nr)
where (order_nr, oDate) in  
    (select order_nr, max(oDate)
     from order group by order_nr)

I tried UNION like i said above and all sorts of things and i dont know what to do also im a noob in sql

CodePudding user response：

Subqueries can only return scalar values like your count see query below,

Of you nned more column you need to join the query

SELECT DISTINCT order.ordner_nr,name,oDate,             
(
SELECT  
   COUNT(*) 
FROM
     order o1
WHERE
   o1.order_nr = order.order_nr
) as ammountOrders
FROM user
INNER JOIN order
using (order_nr)
where (order_nr, oDate) in  
    (select order_nr, max(oDate)
     from order group by order_nr)