Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Adding to a distinct count query

Posted on 2011-02-10
11
Medium Priority
?
665 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

662 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