Solved

Adding to a distinct count query

Posted on 2011-02-10
11
660 Views
Last Modified: 2013-11-28
I am currently running the following query to total the distinct dates in the data field and need to add another datafield. Is there a way to add another distinct count from the same source and output on the same query.
My current query:
SELECT x.MemberID, Count(x.data) AS data
FROM (SELECT DISTINCT MemberID, data FROM Table1 WHERE Criteria="2011") AS x
GROUP BY x.MemberID;

I was thinking of some sort of union which also pulls the data fields into single rows within the query. Something like this? Although it is very important that the dates are DISTINCTLY COUNTED.
Select MemberID, "data" as DataEvent, Data as Date from Table1
WHERE (LEN(NZ([data])) > 0)
UNION ALL Select MemberID, "data2" as DataEvent, Data2 from Table1
WHERE (LEN(NZ([data2])) > 0)
ORDER BY MemberID, DataEvent DESC;
0
Comment
Question by:MRG_AL
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34865906
that would give you 2 rows.

if you need 2 columns:

declare @count1 int
declare @count2 int

set @count1 = <query 1>
set @count 2 = <query 2>

select @count1 as Count1, @count2 as count2
0
 

Author Comment

by:MRG_AL
ID: 34865972
I do need columns. From what you entered it looks as though you are saying I need two separate queries in order to calculate two distinct counts. Is that correct?
0
 
LVL 10

Accepted Solution

by:
calpurnia earned 500 total points
ID: 34867058
If I've understood what you're after here, something like this should do the trick:

SELECT z.MemberID, Count(z.newdata) AS CountOfnewdata
FROM (SELECT x.RecordID, x.MemberID,x.data as newdata
FROM Table1 as x
UNION SELECT y.RecordID, y.MemberID, y.data2 as newdata
FROM Table1 as y) as z
GROUP BY z.MemberID;

RecordID is a unique row identifier in Table1 (e.g. an autonumber field). It's needed as Union queries automatically filter out duplicate records, so you need to make sure each row appears unique.
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 10

Expert Comment

by:calpurnia
ID: 34867083
N.B. If you have the same value appearing in data and data2 in the SAME ROW, these will still be treated as duplicates by the Union query. Is this is a possibility you need to cater for?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34869087
do yo realise that this is allowed?

select memberid,count(distinct column1),count(distinct column2)
from table1
where criteria=....
group by memberid
order by memberid

which produces the count of distinct values ....

or even

select memberid,count(distinct case when condition1  then column1
                                                   when condition2 then column1
                                                     ....
                                                   end)
,count(distinct case when condition3  then column2
                                                   when condition4 then column2
                                                     ....
                                                   end)
from table1
where common condition...
group by memberid
order by memberid

if you need some different selection criteria between the "counts"

0
 
LVL 10

Expert Comment

by:calpurnia
ID: 34869961
However, if you're using Access, it doesn't support Count Distinct :(
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34870363
;-( got me there
0
 

Author Comment

by:MRG_AL
ID: 34870564
Calpurnia,
That does work, however when I run the query it shows data and data2 in the same column and I need to be able to show how many distinct dates are in each data field.
0
 

Author Comment

by:MRG_AL
ID: 34870738
Actually it isn't counting the dates as distinct dates even with the unique recordID and it is only counting the first data set.
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 34871019
Looks like I haven't understood what you're after.

Could you post a small sample of your current data and your desired output?
0
 

Author Closing Comment

by:MRG_AL
ID: 34871405
I was able to use this to figure out my end result and have it running as close to what I was going for. Thank you very much for your time and for knowledge!! : )
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question