I'm trying to display some records which meet some certain criteria.
That works fine, but I also want to retrieve the total amount of orders. These are all located in the field 'Amount' from the Orders table.

So if record 1 has got '22' in it's amount field, and record 5 has got '55' in it's amount field.. the total should be 77.

My sql coding looks like this:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate, sum(Orders.Amount) as total
FROM Orders 
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID
where customers.country='Germany'  
order by Orders.Orderdate

But it only retrieves 1 record (due to the summage), when I remove the sum part it retrieves all records.

How can I retrieve all records, as well as the summage ?

You have to do that in two separate queries. SUM is an aggregate function.


SELECT SUM(value) as total FROM table;


If you wanna retrieve the total number of recs then you may try using the aggregate function count in your query.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.