Find common data between groups
I have this data
DeveloperId Date remark
--------------------------------------
1 02/01/2013 Google
1 02/02/2013 MSN
1 02/03/2013 Google
1 02/02/2013 MSN
1 02/01/2013 Yahoo
2 02/01/2013 Google
2 02/02/2013 Yahoo
2 02/03/2013 Google
2 02/01/2013 Google
How to find common dates for Google and Msn and yahoo dynamically?
I would like to put such a request in the report.
+3
HawkEye13
source
to share
5 answers
Try the following:
with temp as
(
SELECT Date, Remark
From MyTable
GROUP BY Date, Remark
)
SELECT Date
FROM temp
GROUP BY Date
Having COUNT(*) > 1
Working fiddle here: http://sqlfiddle.com/#!3/d41d8/9105
+1
Abe miessler
source
to share
-- Query for dates that have all types of remarks
SELECT
Date
FROM
data
GROUP BY
Date
HAVING COUNT(DISTINCT remark) = (
SELECT COUNT(DISTINCT remark)
FROM data
);
Or, if this was not a requirement ...
-- Query for dates that have more than one remark.
SELECT
Date
FROM
data
GROUP BY
Date
HAVING COUNT(DISTINCT remark) > 1;
Sql Fiddle
+2
Michael Fredrickson
source
to share
Common dates for three different notes:
SELECT t1.DATE
FROM TABLE T1
INNER JOIN TABLE T2
ON t1.DATE = t2.DATE
AND Remark IN ('google', 'yahoo', 'msn')
GROUP BY t1.DATE, t1.REMARK
HAVING COUNT(t1.DATE) = 3;
+1
aspiring
source
to share
Select Date from data
group by remark, Date
0
user2063626
source
to share
Try:
select "Date"
from myTable
group by "Date"
where remark in ('Google','MSN','Yahoo')
having count(distinct remark) = 3
0
Mark bannister
source
to share