Solved

Adding to a distinct count query

Posted on 2011-02-10
11
661 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 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