Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 672
  • Last Modified:

Adding to a distinct count query

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
MRG_AL
Asked:
MRG_AL
  • 4
  • 4
  • 2
  • +1
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
MRG_ALAuthor Commented:
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
 
calpurniaCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
calpurniaCommented:
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
 
LowfatspreadCommented:
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
 
calpurniaCommented:
However, if you're using Access, it doesn't support Count Distinct :(
0
 
LowfatspreadCommented:
;-( got me there
0
 
MRG_ALAuthor Commented:
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
 
MRG_ALAuthor Commented:
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
 
calpurniaCommented:
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
 
MRG_ALAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now