How to get count of each field by a query

[BizBoy]

Hi,

I am writing below query to get distinct of designation from a table.
How do I get count for each designation by editing this query.

For example, one of the field is 'Manager' and count for managers is 70.
How to get count 70 for manager by editing this query.

SELECT DISTINCT Designation
FROM directory
ORDER BY Designation;

Can anyone help me in this please.

 

[Joe4]

Well, Manager is not included in your original query, so you cannot get it from there.
What exactly are you trying to count, the number of distinct managers, or the number of distinct designations?

 

[BizBoy]

Hi Joe,

Sorry for making it confusing.
I am trying to get count of distinct designations.

For example, in my table, I have 2 designations, KTM and Yamaha.
Total KTM in the table are 10 and Yamaha is 20 times.

Distinct will give me values as KTM and Yamaha, however, I need my query to return KTM, Yamaha in one column and 10,20 in next column.
How do I do this.

Have a nice day ahead.

 

[Joe4]

If you want to count the number of instances of each, then you do not want to use DISTINCT (which will weed out all the duplicates occurrences of the value, so your counts would be 1).
Try this:

[COLOR=#333333]SELECT Designation, Count(Designation)[/COLOR]
[COLOR=#333333]FROM directory[/COLOR]
[COLOR=#333333]GROUP BY Designation;[/COLOR]

 

[BizBoy]

Hi Joe,
Thanks for the help.
It is working great.

I need help for below 2 question. Please help only if you get time.
I am trying to Google the same. This is not urgent for me.

1) When I run this query, header of total column is reflecting as 'Exper1001'.
How can we change this field name.

2) I am using below query to get the results.

SELECT Designation, Count(Designation)
FROM Combine
WHERE MyDate = #4/10/2018#
GROUP BY Designation;

MyDate is the first field of table Combine.
This query is giving me result.
Field 1 is Designation
Field 2 is count for 4/10/2018

Is it possible to get details for multiple dates in one query.
For example field 2 will be for 4/10/2018, field 3 will be for 4/9/2018 and so on.

Thanks for help. Have a nice day ahead.

 

[BizBoy]

Hi Joe,

I was able to resolve first issue. Please see below query.
Thanks.

SELECT Designation, Count(Designation) AS Total
FROM Combine
WHERE MyDate = #4/10/2018#
GROUP BY Designation

Copied from

https://stackoverflow.com/questions...ce=google_rich_qa&utm_campaign=google_rich_qa

 

[Joe4]

You can give the calculated field any name you wish, like this:

[COLOR=#333333]SELECT Designation, Count(Designation)[/COLOR][COLOR=#ff0000] as MyCount[/COLOR]
[COLOR=#333333]FROM Combine[/COLOR]
[COLOR=#333333]WHERE MyDate = #4/10/2018#[/COLOR]
[COLOR=#333333]GROUP BY Designation;[/COLOR]

This query is known as an "Aggregate" or "Totals" query. In these type of queries, you have two types of fields:
- fields that you "Group" your data on
- fields that you perform some Aggregate function on (like COUNT, SUM, MAX, MIN, ...)

So, if you want to group by more fields, just add that field to the SELECT and GROUP BY clauses, i.e.

[COLOR=#333333]SELECT Designation, [/COLOR][COLOR=#ff0000]MyDate[/COLOR][COLOR=#333333], Count(Designation)[/COLOR]as MyCount
[COLOR=#333333]FROM Combine[/COLOR]
[COLOR=#333333]GROUP BY Designation, [/COLOR][COLOR=#ff0000]MyDate[/COLOR][COLOR=#333333];[/COLOR]

 

[BizBoy]

Hi Joe,
Sorry for delay in reply. Thanks a lot for the help.

Have a nice day ahead.

 

[Joe4]

You are welcome.