CODEWITHSUNDEEP
sqlsql-server
DutchFatBoys
DutchFatBoys

Reputation: 93

Query with different categories

I want to look if there are more than one QuestionCategory in one day in the table contentment. In my case people don't need to answer in one day questions with different categories. I can make a trigger of this.

The contentmenttable: employeeid, questionid, date, score

The questiontable: questionid, questioncat, question

Data contentmenttable: 1, 1, 11-18-2018, 4

Data questiontable: 1, Work, How is your job? 2, Work, Are you happy with your job?

If have something like this:

select c.questionid, date
from contentment c 
join question q
on c.questionid= q.questionid
group by c.questionid, date
having count(questioncat) >= 2

But this query is only counting IF a questionid is two times or more in this table, not if there are two different questioncategories in this table. I use SQL Server.

So if someone wants to insert this: insert into contentment values (1, 2, 11-18-2018', null) (null because employee needs to give a score) The query needs to give this questionid and date (2 and 11-18-2018), because it is the same questioncat "work" on the same day 11-18-2018.

Upvotes: 0

Views: 92

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Your question is hard to follow, but I think you want employees that have more than one question category in a given day. If so:

select c.employeeid, c.date, count(distinct q.questioncat)
from contentment c join
     question q
     on c.questionid = q.questionid
group by c.employeeid, c.date
having count(distinct q.questioncat) >= 2;

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You need to add DISTINCT:

select c.questionid, date
from contentment c 
join question q
on c.questionid= q.questionid
group by c.questionid, date
having count(DISTINCT questioncat) >= 2;
             -- counting only different questioncat

Upvotes: 1

Related Questions