Select Distinct with multiple count?

[alienz747]

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.

[code=sql]SELECT qryProjects.Fis calYear AS FiscalYear, qryProjects.MyF ac AS HomeFac, Count(qryProjec ts.PayrollNumbe r) AS CountOfPayrollN umber, Count(qryProjec ts.GrantID) AS CountOfProjects , Count(IIF(qryPr ojects.contract _type="Tenured" ,0)) AS CountOfTenured
FROM [SELECT DISTINCT qryProjects.Fis calYear, qryProjects.MyF ac, qryProjects.Pay rollNumber, qryProjects.Gra ntID, qryProjects.con tract_type FROM qryProjects ORDER BY qryProjects.Fis calYear]. AS [%$##@_Alias]
GROUP BY [qryProjects].[FiscalYear, [qryProjects].[MyFac]
HAVING ((([qryProjects].[FiscalYear])>="2003/2004"))
ORDER BY [qryProjects].[FiscalYear];[/code]

[Stewart Ross]

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:

Fac Payroll ID Grant ID Contract Type
A....101..........1234.......Temp
A....101..........2345.......Perm
A....102..........1234.......Temp
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

[alienz747]

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:

Fac Payroll ID Grant ID Contract Type
A....101..........1234.......Temp
A....101..........2345.......Perm
A....102..........5678.......Temp
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

[Stewart Ross]

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