Subqueries with SELECT clause

[wenny]

I may miss some previous knowledge points, why I can’t write the queries in this way, but have to subqueries the AVG part？

[UniqueNospaceShort]

Hi.
Can you give more details on your table? I do not understand its purpose and at 1st sight I wonder if it is not badly designed.

My 1st guess would have to go with mixing columns with aggregate functions (such AVG).

Regards.

EDIT:

I did try something in different fiddle pages and the results differ.

One refuses the query, the other takes it but the result is probably not what you want and returns one line anyway (because of aggregate function).

Schema

CREATE TABLE invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  total INT
);

INSERT INTO invoices (total)
VALUES
(100),
(1000),
(12532),
(15958),
(123447),
(45123),
(9531),
(75121);

The 3 first queries are correct. The last one is smelly to me.

Queries

SELECT id, total FROM invoices;

SELECT total FROM invoices;

SELECT AVG(total) FROM invoices;

SELECT id, total, AVG(total) FROM invoices;

Results

DB Fiddle returns

SQL Fiddle returns

Try for yourself.

[wenny]

Thanks a lot，it really helpful and now i pretty much understand it.

[Karthick_Annamalai]

Hi ,

We need to use “GROUP BY” clause and Aggregate functions (MIN,MAX,SUM,AVG,COUNT) together.
Some times not needed to use both.

Example_1:

SELECT
invoice_id,
invoice_total,
AVG(invoice_total) AS invoice_average
FROM invoices
GROUP BY
invoice_id,
invoice_total

Example_2:

SELECT
invoice_id,
invoice_total,
AVG(invoice_total) OVER (PARTITION BY invoice_id) AS invoice_average
FROM invoices