Count One Field & Sum Another?

[kestefon]

I have a dataset with "Date", "Product ID", and "Revenue" fields. I'd like to create a query that rolls the data up at the daily level and reports "Count of Product ID" (unique product IDs) and "Sum of Revenue". I'm having trouble figuring out the logic.

[June7]

Are you showing this on a spreadsheet just for ease of posting? Really talking about an Access table?

This could require multiple queries.

SELECT DISTINCT [Date], ProductID FROM tablename;

or

SELECT [Date], ProductID FROM tablename GROUP BY [Date], ProductID;

Then:

SELECT [Date], Count(ProductID) AS CountProd FROM query1 GROUP BY [Date];

SELECT [Date], Sum(Revenue) AS SumRev FROM tablename GROUP BY [Date];

Now do a query that joins those two on the common [Date] field.


The 2 product queries can be nested into one:

SELECT [Date], Count(ProductID) AS CountProd FROM (SELECT [Date], ProductID FROM tablename GROUP BY [Date], ProductID) AS query1 GROUP BY [Date];