-2

I want to count distinct value that exist in 2 columns like

Column A    Column B
1             5
5             6
4             1
5             4

How can I get the answer : 4 as there are only four distinct values in both columns.

Can you please guide me how to apply in query (Distinct count in Club1 and Club2)

SELECT Count(DISTINCT registrationevent.RegistrationNo) AS TotalRegistration, Sum(If((registrationevent.Spouse = 1), 1, 0)) AS CoupleRegistration, Count(DISTINCT registration.Club) AS Club1, count(Distinct registration.SpouseClub) as Club2 FROM (registrationevent) JOIN registration ON registrationevent.RegistrationNo = registration.RegistrationNo

Sanjeev
  • 1
  • 2
  • `count distinct same value` - Can you clarify this a little? – Gurwinder Singh Feb 23 '17 at 18:47
  • count distinct values from both column. – Sanjeev Feb 23 '17 at 19:05
  • Possible duplicate of [How do I (or can I) SELECT DISTINCT on multiple columns?](http://stackoverflow.com/questions/54418/how-do-i-or-can-i-select-distinct-on-multiple-columns) – Juliën Feb 23 '17 at 19:13
  • The specification "distinct value in both column jointly" isn't very clear. Do you want to return 4 because there are four distinct values 1,4,5 and 6 that in appear in either A or B? Or did you want to return 4 because there are four distinct (A,B) tuples (1,5), (5,6), (4,1), (5,4). – spencer7593 Feb 23 '17 at 19:24

2 Answers2

1

AFAIK union is distinct by itself:

SELECT A
FROM tab
UNION
SELECT B
FROM tab

or- as you seem to be interested in the count only:

SELECT COUNT(*)
FROM 
  (
    SELECT A
    FROM tab
  UNION
    SELECT B
    FROM tab
  ) AS C
RuDevel
  • 694
  • 3
  • 14
  • Can you please guide me how to apply in this query (Distinct count in Club1 and Club2) SELECT Count(DISTINCT registrationevent.RegistrationNo) AS TotalRegistration, Sum(If((registrationevent.Spouse = 1), 1, 0)) AS CoupleRegistration, Count(DISTINCT registration.Club) AS Club1, count(Distinct registration.SpouseClub) as Club2 FROM (registrationevent) JOIN registration ON registrationevent.RegistrationNo = registration.RegistrationNo – Sanjeev Feb 24 '17 at 08:22
1

Do you mean something like this?

select count(*)
from (
    select distinct * from t
) t
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • I want like this but some other count columns are there in which it affect like in the same table Registration, Gender and etc fields are there and I want to count all in same query – Sanjeev Feb 23 '17 at 19:02
  • @Sanjeev - Use asterisk to select distinct of all the columns or if you want to select some of them, mention them explicitly in the subquery). Updated the answer. – Gurwinder Singh Feb 23 '17 at 19:06
  • Can you please guide me how to apply in this query (Distinct count in Club1 and Club2) SELECT Count(DISTINCT registrationevent.RegistrationNo) AS TotalRegistration, Sum(If((registrationevent.Spouse = 1), 1, 0)) AS CoupleRegistration, Count(DISTINCT registration.Club) AS Club1, count(Distinct registration.SpouseClub) as Club2 FROM (registrationevent) JOIN registration ON registrationevent.RegistrationNo = registration.RegistrationNo – Sanjeev Feb 24 '17 at 07:56