Adding up row values

  • Thread starter Thread starter kthomas
  • Start date Start date
K

kthomas

I am trying to add up all "fertilizer" costs by customer name. However, I am
getting 3 results per customer due to there being 3 types of fertilizer. How
can I combine into one customer, one fertilizer total sales? Code below.

SELECT [SL Customers].[Customer Name], [SL Inventory].[Item Description],
Sum([qty]*[unit price]) AS Total
FROM (([SL Customers] RIGHT JOIN [SL Invoices] ON [SL Customers].[Customer
Number] = [SL Invoices].[Customer Number]) RIGHT JOIN [SL Items Sold] ON [SL
Invoices].[SOP Number] = [SL Items Sold].[SOP Number]) LEFT JOIN [SL
Inventory] ON [SL Items Sold].[Item Number] = [SL Inventory].[Item Number]
GROUP BY [SL Customers].[Customer Name], [SL Inventory].[Item Description]
HAVING ((([SL Inventory].[Item Description]) Like "*fertilizer*"))
ORDER BY Sum([qty]*[unit price]) DESC;
 
How can I combine into one customer, one fertilizer total sales?
Drop [SL Inventory].[Item Description] from the query.

SELECT [SL Customers].[Customer Name], Sum([qty]*[unit price]) AS Total
FROM (([SL Customers] RIGHT JOIN [SL Invoices] ON [SL Customers].[Customer
Number] = [SL Invoices].[Customer Number]) RIGHT JOIN [SL Items Sold] ON [SL
Invoices].[SOP Number] = [SL Items Sold].[SOP Number]) LEFT JOIN [SL
Inventory] ON [SL Items Sold].[Item Number] = [SL Inventory].[Item Number]
GROUP BY [SL Customers].[Customer Name]
HAVING ((([SL Inventory].[Item Description]) Like "*fertilizer*"))
ORDER BY Sum([qty]*[unit price]) DESC;
 
But that brings in all items purchased by customer when I only need
fertilizer items by customer?

KARL DEWEY said:
Drop [SL Inventory].[Item Description] from the query.

SELECT [SL Customers].[Customer Name], Sum([qty]*[unit price]) AS Total
FROM (([SL Customers] RIGHT JOIN [SL Invoices] ON [SL Customers].[Customer
Number] = [SL Invoices].[Customer Number]) RIGHT JOIN [SL Items Sold] ON [SL
Invoices].[SOP Number] = [SL Items Sold].[SOP Number]) LEFT JOIN [SL
Inventory] ON [SL Items Sold].[Item Number] = [SL Inventory].[Item Number]
GROUP BY [SL Customers].[Customer Name]
HAVING ((([SL Inventory].[Item Description]) Like "*fertilizer*"))
ORDER BY Sum([qty]*[unit price]) DESC;

--
Build a little, test a little.


kthomas said:
I am trying to add up all "fertilizer" costs by customer name. However, I am
getting 3 results per customer due to there being 3 types of fertilizer. How
can I combine into one customer, one fertilizer total sales? Code below.

SELECT [SL Customers].[Customer Name], [SL Inventory].[Item Description],
Sum([qty]*[unit price]) AS Total
FROM (([SL Customers] RIGHT JOIN [SL Invoices] ON [SL Customers].[Customer
Number] = [SL Invoices].[Customer Number]) RIGHT JOIN [SL Items Sold] ON [SL
Invoices].[SOP Number] = [SL Items Sold].[SOP Number]) LEFT JOIN [SL
Inventory] ON [SL Items Sold].[Item Number] = [SL Inventory].[Item Number]
GROUP BY [SL Customers].[Customer Name], [SL Inventory].[Item Description]
HAVING ((([SL Inventory].[Item Description]) Like "*fertilizer*"))
ORDER BY Sum([qty]*[unit price]) DESC;
Click to expand...
Click to expand...
 
kthomas said:
I am trying to add up all "fertilizer" costs by customer name. However, I am
getting 3 results per customer due to there being 3 types of fertilizer. How
can I combine into one customer, one fertilizer total sales? Code below.

SELECT [SL Customers].[Customer Name], [SL Inventory].[Item Description],
Sum([qty]*[unit price]) AS Total
FROM (([SL Customers] RIGHT JOIN [SL Invoices] ON [SL Customers].[Customer
Number] = [SL Invoices].[Customer Number]) RIGHT JOIN [SL Items Sold] ON [SL
Invoices].[SOP Number] = [SL Items Sold].[SOP Number]) LEFT JOIN [SL
Inventory] ON [SL Items Sold].[Item Number] = [SL Inventory].[Item Number]
GROUP BY [SL Customers].[Customer Name], [SL Inventory].[Item Description]
HAVING ((([SL Inventory].[Item Description]) Like "*fertilizer*"))
ORDER BY Sum([qty]*[unit price]) DESC;
Click to expand...


You need to remove the item description field from the
Select and Group By clauses.

You also should change the Having clause to a Where clause.
 
I am trying to add up all "fertilizer" costs by customer name. However, I am
getting 3 results per customer due to there being 3 types of fertilizer. How
can I combine into one customer, one fertilizer total sales? Code below.

SELECT [SL Customers].[Customer Name], [SL Inventory].[Item Description],
Sum([qty]*[unit price]) AS Total
FROM (([SL Customers] RIGHT JOIN [SL Invoices] ON [SL Customers].[Customer
Number] = [SL Invoices].[Customer Number]) RIGHT JOIN [SL Items Sold] ON [SL
Invoices].[SOP Number] = [SL Items Sold].[SOP Number]) LEFT JOIN [SL
Inventory] ON [SL Items Sold].[Item Number] = [SL Inventory].[Item Number]
GROUP BY [SL Customers].[Customer Name], [SL Inventory].[Item Description]
HAVING ((([SL Inventory].[Item Description]) Like "*fertilizer*"))
ORDER BY Sum([qty]*[unit price]) DESC;
Click to expand...

Change the HAVING to WHERE (which applies before the grouping is done) and
remove Item Description from the GROUP BY clause.

To do this in the query grid change the Totals row under Item Description from
the default Group BY to Where (Access will uncheck the Show checkbox).
 
Back
Top