Member Avatar for Michael_18

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 ?

Edited by pritaeas because: Added markdown.
  • 5 Contributors
  • 4 Replies
  • 336 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by balaji9124

Dani AI

Generated

Short answer: SUM collapses rows, so you either run two queries (simple and fast) or return each order plus the overall total using one of several patterns. is right that two queries are the simplest; ‘s GROUP BY is useful when you want totals per group (for example per customer), but it doesn’t give you every row plus a single grand total.

Derived-table (one extra subquery executed once — works on old MySQL):

SELECT o.OrderID, c.CustomerName, o.OrderDate, o.Amount, totals.total_amount
FROM Orders AS o
JOIN Customers AS c ON o.CustomerID = c.CustomerID
JOIN (
  SELECT SUM(o2.Amount) AS total_amount
  FROM Orders AS o2
  JOIN Customers AS c2 ON o2.CustomerID = c2.CustomerID
  WHERE c2.country = 'Germany'
) AS totals ON 1=1
WHERE c.country = 'Germany'
ORDER BY o.OrderDate;

Window function (cleanest if your MySQL supports it — MySQL 8.0+):

SELECT o.OrderID, c.CustomerName, o.OrderDate, o.Amount,
       SUM(o.Amount) OVER () AS total_for_germany
FROM Orders AS o
JOIN Customers AS c ON o.CustomerID = c.CustomerID
WHERE c.country = 'Germany'
ORDER BY o.OrderDate;

Append a summary row (if you prefer one extra “TOTAL” row at the end):

SELECT o.OrderID, c.CustomerName, o.OrderDate, o.Amount
FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE c.country = 'Germany'

UNION ALL

SELECT NULL, 'TOTAL', NULL, SUM(o2.Amount)
FROM Orders o2 JOIN Customers c2 ON o2.CustomerID = c2.CustomerID
WHERE c2.country = 'Germany'
ORDER BY OrderDate IS NULL, OrderDate;

Notes: use COALESCE(SUM(...),0) to avoid NULL when no rows; check the execution plan with EXPLAIN if the subquery is slow; ensure joins are indexed (Orders.CustomerID, Customers.CustomerID and consider Customers.country if selective). ’s simple SUM and ’s COUNT are useful in single-aggregate queries, but pick the pattern above depending on whether you want totals repeated per row, an appended total row, or two separate result sets.

Recommended Answers

All 4 Replies

Member Avatar for Reverend Jim

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

Member Avatar for KJM_

use GROUP BY

Member Avatar for chuchaykaw18 
SELECT SUM(value) as total FROM table;

$row['total'];
Edited by pritaeas because: Added markdown.
Member Avatar for balaji9124

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.