UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Group By Query, Access 2013    
 
   
duggie
post Jun 28 2019, 08:46 AM
Post#1



Posts: 459
Joined: 14-October 12



I am following this article:

CODE
https://www.quackit.com/microsoft_access/microsoft_access_2013/tutorial/modify_a_query.cfm



In the section:

CODE
Price using totals


point 4. it specifically states:

CODE
You will also need to delete the ProductName column by selecting it and clicking Delete Columns (next to the Totals button on the Ribbon). It should now look like this:


I deliberately did NOT delete the ProductName field and therefore got a different result.

My question is: what has it returned when I did NOT delete ProductName?

Thanks
This post has been edited by duggie: Jun 28 2019, 08:49 AM
Go to the top of the page
 
cheekybuddha
post Jun 28 2019, 09:31 AM
Post#2


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


It will show the total spent per product bought per customer.

Only useful if the customer has bought more than one of any particular product, otherwise it will be the same as the product price.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
nvogel
post Jun 28 2019, 09:39 AM
Post#3



Posts: 976
Joined: 26-January 14
From: London, UK


If you include ProductName and select "Group By" as the option for Total under ProductName then the result will be grouped by ProductName as well as all the other columns you selected. This will probably mean you get more rows in the result because you'll get at least one row per ProductName.

Tip: look at the SQL view of your query rather than the Design view, then you can learn how the SQL query really works.
Go to the top of the page
 
duggie
post Jun 29 2019, 03:45 AM
Post#4



Posts: 459
Joined: 14-October 12



The SQL as per the article is this:

CODE
SELECT Customers.CustomerID, Customers.FirstName, Customers.LastName, Sum(Products.Price) AS SumOfPrice
FROM Products INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Products.ProductID = Orders.ProductID
GROUP BY Customers.CustomerID, Customers.FirstName, Customers.LastName
HAVING (((Sum(Products.Price))>80000))
ORDER BY Sum(Products.Price) DESC;


and produces this result:

CODE
CustomerID    FirstName    LastName    SumOfPrice
6    Philip    Fry    £255,750.99
1    Homer    Simpson    £255,750.99
4    Brian    Griffin    £190,000.99
8    Hubert J.    Farnsworth    £82,000.00
2    Peter    Griffin    £82,000.00


if I included the ProductName, I get this:

CODE
SELECT Customers.CustomerID, Customers.FirstName, Customers.LastName, Sum(Products.Price) AS SumOfPrice, Products.ProductName
FROM Products INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Products.ProductID = Orders.ProductID
GROUP BY Customers.CustomerID, Customers.FirstName, Customers.LastName, Products.ProductName
HAVING (((Sum(Products.Price))>80000))
ORDER BY Sum(Products.Price) DESC;


with this result:

CODE
CustomerID    FirstName    LastName    SumOfPrice    ProductName
6    Philip    Fry    £190,000.99    Venus Carrera
4    Brian    Griffin    £190,000.99    Venus Carrera
1    Homer    Simpson    £190,000.99    Venus Carrera
8    Hubert J.    Farnsworth    £82,000.00    Mars Dreamliner 787
2    Peter    Griffin    £82,000.00    Mars Dreamliner 787


Note it has NOT returned more results.
This post has been edited by duggie: Jun 29 2019, 03:47 AM
Go to the top of the page
 
RJD
post Jun 29 2019, 03:58 AM
Post#5


UtterAccess VIP
Posts: 9,913
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but you get the different totals and the same number of results because of the HAVING clause. It limits result records to > 80,000. So the other product totals for Fry and Simpson, beyond 190,000.99 shown have been eliminated. Remove the criteria and you will see what is not showing in your last example.

This is an artifact of both the criteria and the data records you are working with, not a problem with Access.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
cheekybuddha
post Jun 29 2019, 04:02 AM
Post#6


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


My guess is that if you remove this line from your query, you will see more records:
CODE
HAVING (((Sum(Products.Price))>80000))


CODE
SELECT
  c.CustomerID,
  c.FirstName,
  c.LastName,
  Sum(p.Price) AS SumOfPrice,
  p.ProductName
FROM Products p
INNER JOIN (Customers c
  INNER JOIN Orders o
          ON c.CustomerID = o.CustomerID)
        ON p.ProductID = o.ProductID
GROUP BY
  c.CustomerID,
  c.FirstName,
  c.LastName,
  p.ProductName
ORDER BY
  Sum(p.Price) DESC;

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 29 2019, 04:04 AM
Post#7


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Ah, Joe got there before me. thumbup.gif

(Bit early/late for you, Joe?)

--------------------


Regards,

David Marten
Go to the top of the page
 
RJD
post Jun 29 2019, 04:11 AM
Post#8


UtterAccess VIP
Posts: 9,913
Joined: 25-October 10
From: Gulf South USA


Hi David! Yes, way early! 4 a.m. my time. Woke up and could not sleep, so thought I'd at least be productive. coffee1.gif

Regards from across the pond ...

Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
duggie
post Jun 29 2019, 03:46 PM
Post#9



Posts: 459
Joined: 14-October 12



Thanks to all.

I didn't think there was a problem, it's just my limited knowledge of SQL.

I was of the understanding that when you do an aggregate calculation, all other fields must be grouped by and in doing so, resulted in a different answer.

The article also didn't explain why the ProductName field had to be dropped.
This post has been edited by duggie: Jun 29 2019, 03:50 PM
Go to the top of the page
 
RJD
post Jun 29 2019, 04:30 PM
Post#10


UtterAccess VIP
Posts: 9,913
Joined: 25-October 10
From: Gulf South USA


QUOTE
I was of the understanding that when you do an aggregate calculation, all other fields must be grouped by and in doing so, resulted in a different answer.

Well, yes, all SELECT fields must be accounted for, either by including them in the GROUP BY or using an aggregate code (Sum, Count, Max, etc.). In your case you got caught by the HAVING limit when you added the ProductName to the SELECT and GROUP BY. That eliminated some of the products within customer whose sums were less than or equal to 80,000. Did you remove the HAVING clause and see what that did to your results (as a learning vehicle)?

Good on you to experiment with the Totals query (GROUP BY). Trying different combinations will show you better than just reading about it. In some cases it can be a bit tricky.

And you are very welcome - from all of us. Let us know if we can be of further assistance as you explore this and other SQL approaches.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
duggie
post Jun 30 2019, 01:24 AM
Post#11



Posts: 459
Joined: 14-October 12



Thanks for your explanation.

I tried your suggestion, ie added the ProductName field AND also removing the HAVING (((Sum(Products.Price))>80000)) clause but I got a third set of results! I agree it is the "complete" set, eg Philip Fry has two products at £190,000.99 and £65,750, which totals to £255,750.99.

CODE
SELECT Customers.CustomerID, Customers.FirstName, Customers.LastName, Sum(Products.Price) AS SumOfPrice, Products.ProductName
FROM Products INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Products.ProductID = Orders.ProductID
GROUP BY Customers.CustomerID, Customers.FirstName, Customers.LastName, Products.ProductName
ORDER BY Sum(Products.Price) DESC;



CODE
CustomerID    FirstName    LastName    SumOfPrice    ProductName
6    Philip    Fry    £190,000.99    Venus Carrera
4    Brian    Griffin    £190,000.99    Venus Carrera
1    Homer    Simpson    £190,000.99    Venus Carrera
8    Hubert J.    Farnsworth    £82,000.00    Mars Dreamliner 787
2    Peter    Griffin    £82,000.00    Mars Dreamliner 787
6    Philip    Fry    £65,750.00    Saturn SUV
1    Homer    Simpson    £65,750.00    Saturn SUV
10    Bender    Rodriguez    £55,000.00    Mercury Riser 2020
5    Cosmo    Kramer    £55,000.00    Mercury Riser 2020
3    Stewie    Griffin    £35,000.00    Mars Daytripper
10    Bender    Rodriguez    £25,000.00    Pluto Mini Racer
7    Amy    Wong    £25,000.00    Pluto Mini Racer


db attached.

Attached File  SpaceTrips.zip ( 40.31K )Number of downloads: 2

This post has been edited by duggie: Jun 30 2019, 01:30 AM
Go to the top of the page
 
cheekybuddha
post Jun 30 2019, 04:02 AM
Post#12


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


>> but I got a third set of results! <<

What did you expect to see?

--------------------


Regards,

David Marten
Go to the top of the page
 
duggie
post Jun 30 2019, 05:14 AM
Post#13



Posts: 459
Joined: 14-October 12



This third set of results is what I would have created myself in response to the original question (and therefore I'd be wrong)!

CODE
CustomerID    FirstName    LastName    SumOfPrice    ProductName
6    Philip    Fry    £190,000.99    Venus Carrera
4    Brian    Griffin    £190,000.99    Venus Carrera
1    Homer    Simpson    £190,000.99    Venus Carrera
8    Hubert J.    Farnsworth    £82,000.00    Mars Dreamliner 787
2    Peter    Griffin    £82,000.00    Mars Dreamliner 787
6    Philip    Fry    £65,750.00    Saturn SUV
1    Homer    Simpson    £65,750.00    Saturn SUV
10    Bender    Rodriguez    £55,000.00    Mercury Riser 2020
5    Cosmo    Kramer    £55,000.00    Mercury Riser 2020
3    Stewie    Griffin    £35,000.00    Mars Daytripper
10    Bender    Rodriguez    £25,000.00    Pluto Mini Racer
7    Amy    Wong    £25,000.00    Pluto Mini Racer


Then with this third set, I'd try to figure a way to aggregate the SumOfPrice field, so that, for example, Philip Fry would not show two entries of £190K and £65K but a combined of £225K.

How I would do this would be a case of trial and error.

But with the explanation by yourself and others here, I think I finally understand why the ProductName field must be dropped to create the correct result.

The reason is we are trying to aggregate by the person. If instead we are trying to aggregate by the ProductName, we would have to drop the CustomerID, FirstName and LastName fields.
This post has been edited by duggie: Jun 30 2019, 05:21 AM
Go to the top of the page
 
cheekybuddha
post Jun 30 2019, 05:33 AM
Post#14


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Having ProductName included in the SELECT/GROUP BY distinguishes the 2 records for Philip Fry.

If you just want to see his total spend then you can not include the ProductName (not like this, anyway):
CODE
SELECT
  c.CustomerID,
  c.FirstName,
  c.LastName,
  COUNT(*) AS OrderCount,
  SUM(p.Price) AS SumOfPrice
FROM Products p
INNER JOIN (Customers c
  INNER JOIN Orders o
          ON c.CustomerID = o.CustomerID)
        ON p.ProductID = o.ProductID
GROUP BY
  c.CustomerID,
  c.FirstName,
  c.LastName
ORDER BY
  Sum(p.Price) DESC;

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 30 2019, 05:41 AM
Post#15


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


>> The reason is we are trying to aggregate by the person. If instead we are trying to aggregate by the ProductName, we would have to drop the CustomerID, FirstName and LastName fields. <<

If you wish to find total sales by Product then you can probably ignore the customer table altogether:
CODE
SELECT
  p.ProductID,
  p.ProductName,
  COUNT(*) AS OrderCount,
  SUM(p.Price) AS Total
FROM Products p
INNER JOIN Orders o
        ON p.ProductID = o.ProductID
GROUP BY
  p.ProductID,
  p.ProductName
ORDER BY
  SUM(p.Price) DESC;

--------------------


Regards,

David Marten
Go to the top of the page
 
RJD
post Jun 30 2019, 01:21 PM
Post#16


UtterAccess VIP
Posts: 9,913
Joined: 25-October 10
From: Gulf South USA


Hi: In case you are interested, you can also list the customers individually (not separately by each sale), with the total order value, plus all the individual orders on the same output record (line). This uses theDBguy's SimpleCSV function. See qryCustomersWithOrdersOnSameLine in the revision to your db attached.

Since you are exploring possibilities, I thought you might like to see this.

And also while you are in learning mode, you might want to consider removing the spaces from object names and preceding each object name with the type object (e.g., tbl, qry, frm, etc...). These things will avoid some possible problems in coding and allow you to better keep up with objects. Just a thought ...

HTH
Joe
Attached File(s)
Attached File  SpaceTrips_Rev1.zip ( 34.64K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th August 2019 - 11:34 AM