Complex Query (1 Viewer)

[pickslides]

Hi there, I wish to write a query that counts the number of unique IDs by date and then sums then by company.

Sheet attached.

For example company D has 26 records but the amount of unique Ids when grouped by date is only 20.

Kind regards, MQ.

 

[jdraw]

Every table in a relational database should have a Primary Key to uniquely identify every record in the table.

Looking at Company D specifically,I get this (but I may have missed your grouping criteria).

Date	CountOfID	Company	SumOfPruchase Amount
29/12/2012	4	D	$3,820.00
30/12/2012	1	D	$283.00
1/01/2013	5	D	$3,646.00
2/01/2013	3	D	$3,901.00
3/01/2013	6	D	$6,117.00
4/01/2013	3	D	$2,195.00
5/01/2013	4	D	$2,739.00

My query

SELECT [Purchase Table].Date, Count([Purchase Table].ID) AS CountOfID
, [Purchase Table].Company
, Sum([Purchase Table].[Pruchase Amount]) AS [SumOfPruchase Amount]
FROM [Purchase Table]
GROUP BY [Purchase Table].Date, [Purchase Table].Company
HAVING ((([Purchase Table].Company)="D"));

 

[pickslides]

Hi jdraw, thanks for your post. This is kinda what I am looking for, but I am after the distinct count of IDs within a date.

So this code:

SELECT [Purchase Table].Company, Count([Purchase Table].ID) AS CountOfID, Sum([Purchase Table].[Pruchase Amount]) AS [SumOfPruchase Amount]
FROM [Purchase Table]
GROUP BY [Purchase Table].Company;

Will give the group, the count of IDs = 26 and the Sum of Purchase amount, but the distinct number of IDs within any date = 20.

For example if ID 13 appears twice on the 1/1/2013 then it is only counted once.

 

[pickslides]

I think i'm getting closer.

I have:

SELECT [Purchase Table].Company, [Purchase Table].[Purchase Date], [Purchase Table].ID, Count([Purchase Table].[ID]) AS IDcount
FROM [Purchase Table]
WHERE ((([Purchase Table].Company)="D"))
GROUP BY [Purchase Table].Company, [Purchase Table].[Purchase Date], [Purchase Table].ID;

Which gives me the 20, as there are now 20 rows! Now I just need to count these rows somehow.

Also note I have changed the field name "Date" to "Purchase Date" to go with convention.

MQ..

 

[jdraw]

Not sure I'm following.

ID	Company	Date	CountOfID
10	D	1/01/2013	1
10	D	2/01/2013	1
10	D	3/01/2013	2
10	D	4/01/2013	1
11	D	29/12/2012	2
11	D	1/01/2013	1
11	D	3/01/2013	1
11	D	4/01/2013	1
12	D	2/01/2013	1
12	D	5/01/2013	2
[COLOR="Red"][B]13	D	3/01/2013	2
13	D	4/01/2013	1[/B][/COLOR]
14	D	29/12/2012	1
14	D	30/12/2012	1
14	D	1/01/2013	2
14	D	3/01/2013	1
15	D	29/12/2012	1
15	D	1/01/2013	1
15	D	2/01/2013	1
15	D	5/01/2013	2

Query is

SELECT   [Purchase Table].ID, [Purchase Table].Company, [Purchase Table].Date, Count([Purchase Table].ID) AS CountOfID
FROM [Purchase Table]
GROUP BY [Purchase Table].ID, [Purchase Table].Company, [Purchase Table].Date
HAVING ((([Purchase Table].Company)="D"));

There are 15 distinct IDs. (identified separately)

 

[pickslides]

Not sure I'm following.

Could be my explanation!

If an ID appears more than once on a certain date it should only be counted as one. Hence in Company D there are 26 records and reduced to 20 records once I count ID when grouped by Purchase Date and ID

All I need to do now is to find a way of counting the rows in the output of this query. I might try to piggy back

 

[pickslides]

I ended up solving the problem by piggy backing some queries. Firstly...

SELECT [Purchase Table].Company, [Purchase Table].[Purchase Date], [Purchase Table].ID, Count([Purchase Table].[ID]) AS IDcount
FROM [Purchase Table]
GROUP BY [Purchase Table].Company, [Purchase Table].[Purchase Date], [Purchase Table].ID;

Then

SELECT [Group by Date and ID].Company, Count([Group by Date and ID].IDcount) AS CountOfIDcount
FROM [Group by Date and ID]
GROUP BY [Group by Date and ID].Company;

Thanks to all that viewed the thread.

MQ.

 

[jdraw]

Glad you have it resolved.