Totals in queries

[wxkeep]

I've got a db - that tracks customers and sales numbers. Each customer may have multiple sales, and thus, multiple entries:

sales

id | Name | Amount
1 John 500
2 Mark 500
3 Luke 100
4 John 200


I run a query on this table that sums the amounts and groups by name so its results:

John 700
Mark 500
Luke 100

And then I run a seperate query that sums all of the sales to give me

1300

Surely there is a way to do all of this with just one query - but it's elluding me.

 

[sxschech]

You can combine the two queries into one with a union query. Since your first query has two fields, you second will also need two fields, perhaps "Total". You might also add a col to create a sort so that the total is the last row. As a simple example:

Select 1 as SortOrder, Name, Amount
From Table
UNION
Select 2, "Total" , Amount
From Table
Order By 1;

 

[Remou]

You can use a Union:

[tt]SELECT tblTable.CoName, Sum(tblTable.Amount) AS SumOfAmount
FROM Table5
GROUP BY tblTable.CoName
Union
SELECT "Total" As Total, Sum(tblTable.Amount) AS SumOfAmount
FROM tblTable;[/tt]