Solved

Adding to a distinct count query

Posted on 2011-02-10
11
664 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 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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

Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

623 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