473,320 Members | 2,080 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Select Distinct with multiple count?

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
3 2350
Stewart Ross
2,545 Expert Mod 2GB
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
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
Stewart Ross
2,545 Expert Mod 2GB
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

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

Similar topics

5
by: Ralph Freshour | last post by:
I have a question about the following PHP script - I got it off a web site tutorial on how to count users logged into your site - my question is the $PHP_SELF variable - it writes the name of the...
1
by: Hunter | last post by:
Hi group - I'm using php to extract data from postgres and print to browser. I have data that was input through checkboxes on a form - each checkbox had a different value but got pushed into the...
2
by: mfyahya | last post by:
I have two tables, both containing an 'authors' column. Is there a way to get a unique list of authors from the two tables? I tried SELECT DISTINCT `authors` from `table1`, `table2`; but I got an...
6
by: RCS | last post by:
I've been running into more and more complexity with an application, because as time goes on - we need more and more high-level, rolled-up information. And so I've created views, and views that use...
8
by: Rich | last post by:
My table looks like this: char(150) HTTP_REF, char(250) HTTP_USER, char(150) REMOTE_ADDR, char(150) REMOTE_HOST, char(150) URL, smalldatetime TIME_STAMP There are no indexes on this table...
2
by: Chris | last post by:
Hello all, I'm having some trouble setting up a query. Background: The table TBLSCREEN stores data about screenings of patients/subjects for eligibility to participate in a health study. A...
2
by: Michael Howes | last post by:
I have a single DataTable in a DataSet. It has 4 columns and i'd like to get a handful of counts of unique items in 3 of the 4 columns. Can a DataTables Select or Compute methods to COUNT DISTINCT?...
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
9
by: P3Eddie | last post by:
Hello all! I don't know if this can even be done, but I'm sure you will either help or suggest another avenue to accomplish the same. My problem may be a simple find duplicates / do something...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.