Why is only one result showing from my query?

Question

Why am I only getting one result from the query below? The suggested "answer" has the first name "Susan" instead of what I got in my results.

SELECT EmpFirstName, EmpLastName, p.ProductName as ProductName, 
YEAR(c.OrderDate) AS Year, 
SUM(o.QuotedPrice + o.QuantityOrdered) AS TotalValue 
FROM Employees 
NATURAL JOIN Products p 
NATURAL JOIN Order_Details o 
NATURAL JOIN Orders c 
ORDER BY Year, TotalValue DESC

Image of results

Image of Table Structure

Answer 1

I have here the "same problem" but with count() so I add my problem inside a subquery and works fine. Remember put all condition including where inside the subquery to work properly. Try something like:

SELECT M.Id, M.Name,
(SELECT count(L.Id) FROM tblog as L where L.Id = M.Id) as qttlog 
from `tbmember` as M
where M.Id <> 0 and M.Name <> '' 
ORDER BY M.Id asc;

change for sum() -

your code:

SELECT EmpFirstName, EmpLastName, p.ProductName as ProductName, 
YEAR(c.OrderDate) AS Year, 
(SUM(o.QuotedPrice + o.QuantityOrdered) FROM Order_Details as o WHERE 'condition here') AS TotalValue 
FROM Employees 
NATURAL JOIN Products p 
NATURAL JOIN Orders c 

WHERE 'condition here'

ORDER BY Year;

I've made the changes in your code and remove TotalValue DESC because de reference doesn't work (idk why) but the column will have the name.

You forgot the where clause before order by

and you need to inform too the where clause inside the subquery, do not forget that!

I hope it helps you

P.S.:

inside the subquery you can compare with all the atributes selected before de subquery (note the coma only before the subquery - because it's the last element selected) Look first code, I compare with M.Id selected before the subquery)

• remember that: where clause make everything more safe, mainly update and delete

I found something, maybe helps more: after ORDER BY Year,

 (SUM(o.QuotedPrice + o.QuantityOrdered) FROM Order_Details as o WHERE 'condition here') ASC ;

this makes the reference in where clause and works with order by too!

Answer 2

The result returned by the query does not match your expectations because the query is invalid. And your expectations are incorrect.

The presence of an aggregate (GROUP BY) function in the expression from the SELECT clause requires the presence of a GROUP BY clause. When such a clause does not exists, the SQL standard automatically adds a GROUP BY 1 clause that produces only one group from all the selected rows.

Each expression that appears in the SELECT clause of a GROUP BY query must follow one of these rules, in order to have a valid SQL query:

1. it also appears in the GROUP BY clause;
2. it's a call to an aggregate (GROUP BY) function;
3. is functionally dependent of one column that appears in the GROUP BY clause.

Because your query does not have a GROUP BY clause, the expressions EmpFirstName, EmpLastName, p.ProductName and YEAR(c.OrderDate) are not valid in the SELECT clause.

Before version 5.7.5, MySQL used to allow such invalid SQL queries but it reserved its privilege to return indeterminate values for the invalid expressions.

Since version 5.7.5, MySQL handles such queries correctly and rejects them. Other RDBMS-es handle them correctly since many years ago.

The explanation for the indeterminate values is simple: the JOIN and WHERE clauses extract some rows from the table(s). The (missing) GROUP BY clause produces only one record from all these rows. A GROUP BY query never returns rows from the table, it generates the values it puts in the result set. Since there are multiple different values for EmpFirstName in the group, the SQL standard says the query is invalid. MySQL used to ignore the standard but it had no valid rule about what value to pick from the EmpFirstName expression in the SELECT clause. Any value from the rows in the group is equally valid and that's what it returns: one random value from the group.

In order to get the results you expect you have to group the rows by OrderNumber and ProductNumber (and EmployeeID to get a valid SQL query):

Answer 3

Because there are a Sum in your Query