Query for a count of a field which is not unique

[BittenApple]

Hello team,
I am going to write a query for these fields below:
ID GroupID Date
1 11 11/11/2014
2 11 11/11/2014
3 11 11/11/2014
4 11 11/12/2014
5 11 11/12/2014
6 7 01/01/2016

I grouped by GroupID and Date and eyeball it to see the counts for each GroupID,

But:
If I want to group by group ID and the count of Date, then for GroupID 11, then I will have 3 counts for date 11/11/2014 even if I use distinct word.
I need the date 11/11/2014 to be counted only one time, not 3 times.
Any help is greatly appreciated.

Regards,
BittenApple

[HansV]

You first need to group by GroupID and by Date:

SELECT GroupID, [Date] FROM MyTable GROUP BY GroupID, [Date]

where MyTable is the name of your table. You can use this as a subquery to get the count that you want:

SELECT GroupID, Count(*) As DistinctDates FROM (SELECT GroupID, [Date] FROM MyTable GROUP BY GroupID, [Date]) GROUP BY GroupID

[BittenApple]

Hello Hans,
Wow, it worked!
But,
I need to add three fields to this query:
1-GroupName which is placed in another table (assume table a) and it can be joined to the first query through GroupID
2-ProcessName which is placed in another table (assume table b) and It can be joined to the first query through processID

I mean mytable has two fields as GroupID and ProcessID that I want to bring GroupName and ProcessName from other tables and add to the query that you sent.

Why wouldn't you add the name of the table preceding the name of fields?

Regards,
BittenApple

[HansV]

The SQL statement that I posted is based on one table only, so it isn't really necessary to specify the table name for each field.

We can't include ProcessID in the query since that would probably mess up the counts. So we can't join to table b.
You can create a new query based on the query that I posted and table a, joined on GroupID. Add all fields from the query to the query grid, plus the GroupName field from table a.

[BittenApple]

Hello Hans,
I created the second query; and, it worked. I want to change the second query to parameter query. Do I have to run the first query preceding the second query or first query runs as we run the second query where query first is part of that?

Secondly:
I know this part:
(SELECT GroupID, [Date] FROM MyTable GROUP BY GroupID, [Date])

But I don't know how the query and subquery are related,
Does SELECT GroupID, [Date] FROM MyTable GROUP BY GroupID, [Date] return yes or no? Or does it return grouped groupID and grouped Date, then outer query gives us the groupId and count of records for that groupID?

Regards,
BittenApple

[HansV]

What parameter do you want to use?

But I don't know how the query and subquery are related,
Does SELECT GroupID, [Date] FROM MyTable GROUP BY GroupID, [Date] return yes or no? Or does it return grouped groupID and grouped Date, then outer query gives us the groupId and count of records for that groupID?

The latter: it returns the unique combinations of GroupID and Date.