SQL - Subquery in Aggregate Function

Subqueries are not generally allowed in aggregate functions. Instead, move the aggregate inside the subquery. In this case, you'll need an extra level of subquery because of the top 5:

SELECT c.CategoryName,
  (select sum(val)
   from (SELECT TOP 5 od2.UnitPrice*od2.Quantity as val
         FROM [Order Details] od2, Products p2
         WHERE od2.ProductID = p2.ProductID
         AND c.CategoryID = p2.CategoryID
         ORDER BY 1 DESC
        ) t
  )
FROM [Order Details] od, Products p, Categories c, Orders o 
WHERE od.ProductID = p. ProductID
AND p.CategoryID = c.CategoryID
AND od.OrderID = o.OrderID
AND YEAR(o.OrderDate) = 1997
GROUP BY c.CategoryName, c.CategoryId

Subquery in an aggregate function

Use join instead :

SELECT t.id,MAX(t.date), COALESCE(s.max_date,DefaultDateHerE) as max_date_t2
FROM table1 t
LEFT JOIN(SELECT p.id,MAX(p.date) as max_date
          FROM Table2
          GROUP BY p.id) s
 ON(s.id = t.id)
GROUP BY t.id,COALESCE(s.max_date,DefaultDateHerE)

SQL: How to implement aggregate function into Subquery

I would recommend putting the subquery in the FROM clause, because you need to refer to it twice.

The key idea is that you need something to get the average per department. Something like this:

SELECT e.empno, e.ename, e.sal, d.avg_sal
FROM emp e JOIN
     (SELECT e.deptid, AVG(sal) as avg_sal
      FROM emp e
      GROUP BY e.deptid
     ) d
     ON e.dept_id = d.dept_id
WHERE e.sal > d.avg_sal;

In particular, you do not need an outer GROUP BY, because you are not aggregating anything at the employee level.

Aggregate/Sum function on a subquery

If the query really gives you the sub results you are after, then all you have to do is put the query in a subquery and aggregate again:

SELECT
  asofdate,
  SUM(monthly_return)
FROM ( <your query here> ) subquery
GROUP BY asofdate
ORDER BY asofdate;

As it is kind of rare that you are grouping by all columns you are selecting, I am showing you some sample data and the results, so you can double check:

With this data

AsOfDate	ColumnA	ColumnB	ColumnC	ColumnD
2021-05-25	12	2	3	123
2021-05-25	12	2	3	123
2021-05-25	12	2	3	123
2021-05-25	12	1	3	123
2021-05-25	12	1	3	123
2021-05-25	12	1	3	123

SQL Subquery with aggregate function - seeking alternative

You can use window functions. Starting from your existing conditional aggregation query, this computes the consumption difference between the current and previous date for each house:

select
    date,
    house_a,
    coalesce(house_a - lag(house_a) over(order by date), 0) daily_a,
    house_b,
    coalesce(house_b - lag(house_b) over(order by date), 0) daily_b,
    house_c,
    coalesce(house_c - lag(house_c) over(order by date), 0)daily_c
from (
    select 
        date, 
        sum(case when houses = 'House_A' then kw_measure end) as house_a,
        sum(case when houses = 'House_B' then kw_measure end) as house_b,
        sum(case when houses = 'House_C' then kw_measure end) as house_c
    from table1
    group by date
) t
order by date

The subquery is not technically necessary, but it avoids repeating the conditional sum()s twice.

Understanding JOINS , Sub Query and Aggregate Functions

SQL only supports one aggregation at a time; for multiple aggregations you need multiple subqueries/CTEs. If you want the average number of people in the cities, then you are almost there:

SELECT AVG(inner_count_city * 1.0) from 
FROM (SELECT   city  AS  inner_city, COUNT(*) AS inner_count_city
      FROM  employees
      GROUP BY inner_city
     ) c;

Note that SQL Server does integer arithmetic on integers. So the average of 1 and 2 is 1, not 1.5. Hence the * 1.0.

You can do this without a subquery as well:

SELECT COUNT(*) * 1.0 / COUNT(DISTINCT city)
FROM employees;

How do i select only a column from my sub-query with aggregate function?

You could use an inner join on subquery

  SELECT      PROD_SUBCAT_CODE, SUM(TOTAL_AMT)[SUM], AVG(TOTAL_AMT)[AVG]
  FROM        TRANSACTIONS
  INNER JOIN (
        SELECT      
        TOP 5       PROD_CAT_CODE, SUM(T1.QTY) [Quantity Sold]
        FROM        TRANSACTIONS
        GROUP BY    PROD_CAT_CODE
        ORDER BY    SUM(T1.QTY)
        DESC
        ) T ON T.PROD_CAT_CODE = TRANSACTIONS.PROD_CAT_CODE
  GROUP BY    PROD_SUBCAT_CODE
  ORDER BY    PROD_SUBCAT_CODE

Accessing aggregate in nested subquery

One way would be to run a window function over the aggregate function in a subquery:

SELECT order_id, sumtotal
FROM  (
   SELECT order_id
        , SUM(extended_price) AS sumtotal
        , AVG(SUM(extended_price)) OVER () AS avgtotal
   FROM   order_lines
   GROUP  BY order_id
   ) sub
WHERE  sumtotal > avgtotal;

Should be faster.

Or use a CTE to avoid repeated evaluation. It adds some cost for the materialization of the interim result, though.

WITH cte AS (
   SELECT order_id, SUM(extended_price) AS sumtotal
   FROM   order_lines
   GROUP  BY order_id
   )
SELECT order_id, sumtotal
FROM   cte
WHERE  sumtotal > (SELECT avg(sumtotal) FROM cte);

You might use another CTE for the average for clarity, but subqueries are generally cheaper.

Postgres window function and group by exception
Best way to get result count before LIMIT was applied
PostgreSQL reusing computation result in select query

SQL subquery and aggregate function

This is all you need, your inner query.

Select OrderID
FROM OrderDetails
GROUP BY OrderID
HAVING COUNT(*) > 1

And if you want the other fields from the main Order table for these Orders with multiple products, then use the following.

SELECT * 
FROM Orders
WHERE OrderId IN ( 
    SELECT OrderID
    FROM OrderDetails
    GROUP BY OrderID
    HAVING COUNT(*) > 1
    )

SQL - Subquery in Aggregate Function