Clever Geek Handbook

How to avoid groups but requires a minimum bill?

I have answered and read many questions about how to get the largest number of n-groups, but now I need the opposite.

I have a result set that shows students, date and project, which represent which students were working on a project on a specific day.

I would like to see lines in which several students have been working on a project for this day. So if my result set looks like this:

| student |    date    | project |
+---------+------------+---------+
|    1    | 2014-12-04 |    1    |
|    2    | 2014-12-04 |    1    |
|    3    | 2014-12-04 |    1    |
|    1    | 2014-12-03 |    1    |

I would love to see the first three lines, so I see that students 1,2,3 were working together on the same project on the same day. I could filter like this:

GROUP BY date, project
HAVING COUNT(*) > 1

But then only one row will be returned.

+3


source to share


2 answers


you can use an existing query as a subquery and get the results

SQL FIDDLE DEMO



SELECT * from Table1 T1
JOIN
(
  SELECT date, project
  from table1
  group by date, project
  having count(*) >1
) t
on t1.date = t.date
and t1.project = t.project
+2


source


This should work.

I think of a table as two datasets and I am joining them based on date and project, not the same student.



That way, if there are any records after the merge, we know they have the same project and date, but not for the same student. Group the results ... and you have what you need.

SELECT A.student, A.date, A.project
from table a
INNER JOIN table b
on A.date=B.Date 
and A.Project=B.Project
and a.student<> b.student
group by A.student, a.date, a.project
+1


source






More articles:


All Articles