By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,847 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,847 IT Pros & Developers. It's quick & easy.

Select Distinct with multiple count?

P: 2
Hi,

I have a query which is supposed to count distinct fields but it doesn't work properly. It does not count the unique values of the PayrollNumber. It counts all values. Any ideas why? Thanks.

Expand|Select|Wrap|Line Numbers
  1. SELECT qryProjects.FiscalYear AS FiscalYear, qryProjects.MyFac AS HomeFac, Count(qryProjects.PayrollNumber) AS CountOfPayrollNumber, Count(qryProjects.GrantID) AS CountOfProjects, Count(IIF(qryProjects.contract_type="Tenured",0)) AS CountOfTenured
  2. FROM [SELECT DISTINCT qryProjects.FiscalYear, qryProjects.MyFac, qryProjects.PayrollNumber, qryProjects.GrantID, qryProjects.contract_type FROM qryProjects ORDER BY qryProjects.FiscalYear]. AS [%$##@_Alias]
  3. GROUP BY [qryProjects].[FiscalYear, [qryProjects].[MyFac]
  4. HAVING ((([qryProjects].[FiscalYear])>="2003/2004"))
  5. ORDER BY [qryProjects].[FiscalYear];
May 8 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
Hi alienz747. The grouping of your subquery is on fiscal year and faculty. You would also need to group on payroll number if you want to count the payroll numbers, and you would have to leave out the grant ID and contract type fields as these further discriminate the distinct rows returned.

You may mistakenly think you can count different groupings of the payroll number, the contract type and the grant IDs in one and the same SQL statement - but this is not so. The count will return the count of the number of distinct rows within that grouping, and as you have included the grant ID and the contract type you will count all rows returned for that faculty in that fiscal year. The name of the field you count on does not really matter - it is the grouping of the fields (after dropping of any duplicates by the DISTINCT clause) which determines the count.

If you consider the example below you may see what I mean more clearly. If this was a sample of the data returned by your subquery:
Expand|Select|Wrap|Line Numbers
  1. Fac Payroll ID Grant ID Contract Type
  2. A....101..........1234.......Temp
  3. A....101..........2345.......Perm
  4. A....102..........1234.......Temp
  5. A....102..........3456.......Temp
then a count of the Payroll ID would yield 4, as will a count of the grant ID and the contract type.

-Stewart
May 8 '08 #2

P: 2
Hi Stewart,

Yes, I thought I could count many different groupings in the same SQL statement. Thanks for clarifying that for me.

If you consider the example below you may see what I mean more clearly. If this was a sample of the data returned by your subquery:
Expand|Select|Wrap|Line Numbers
  1. Fac Payroll ID Grant ID Contract Type
  2. A....101..........1234.......Temp
  3. A....101..........2345.......Perm
  4. A....102..........5678.......Temp
  5. A....102..........3456.......Temp
then a count of the Payroll ID would yield 4, as will a count of the grant ID and the contract type.

-Stewart
In my case, all the Grant IDs are unique. A faculty member could have more than one grant (so associated with more than one Grant ID). Considering the data above: what I need is a query which will yield a count of 2 for Payroll Number, 4 for Grant ID, etc (have many other groupings which I did not show in the post). Could you please help me understand how to do this? My other alternative is to run separate queries for each grouping. This would be painful :(

Kate
May 8 '08 #3

Expert Mod 2.5K+
P: 2,545
Hi Kate. There is no easy alternative to using separate query groupings, I'm sorry to say. Personally, I'd redesign your queries to run on as minimal a set of groupings as possible, then cascade one or more base queries to feed a totals query for each group, totalling within the one grouping only. A subquery approach might be possible, but I think it will just obscure and complicate the logic of what you need.

There is no SQL equivalent to the Excel CountIF and SUMIF functions which perform conditional counts and SUMs on data; had there been you could have continued with the one SQL statement, but as there is no such functionality you will indeed have to use separate queries.

-Stewart
May 8 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.