How to make distinct when column is not in the view

Question

How to make distinct when column is not in the view

craig.jenkins

SSCommitted

Points: 1643
More actions
November 17, 2017 at 6:36 am

#402679

Guys, is there a way for me to show the results of the below where there is a distinct Q.REGNO in the data without showing the REGNO in the results. Hope that makes sense
SELECT Q.Dealer , Q.CSISeqNo
,Q2.Question
--BRISTOL 1 STAR
    ,SUM (CASE WHEN Q2.CSISeqNo = 94 AND A.Description = '1 Star'
    OR Q2.CSISeqNo = 107 AND A.Description = '1 Star'
    OR Q2.CSISeqNo = 95 AND A.Description = '1 Star'
    OR Q2.CSISeqNo = 96 AND A.Description = '1 Star'

THEN 1 ELSE 0 END ) AS [1 Star]
FROM CSIQuestionsLog Q
JOIN CSIQuestions Q2 on Q.CSISeqNo=Q2.CSISeqNo
JOIN LogFile L on Q.LogSeqNo=L.Seq
JOIN CSIAnswers A on A.CSISeqNo=Q2.CSISeqNo AND A.AnswerId=Q.Answer
WHERE Q.Created >='2017-10-01'AND Q.Created <'2017-11-01'
AND Q.Dealer IN ('BA')
GROUP BY Q.Dealer, Q2.Question, Q.CSISeqNo
ORDER BY Q.Dealer, Q.CSISeqNo

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

craig.jenkins - Friday, November 17, 2017 6:36 AM
Guys, is there a way for me to show the results of the below where there is a distinct Q.REGNO in the data without showing the REGNO in the results. Hope that makes sense
SELECT Q.Dealer , Q.CSISeqNo
,Q2.Question
--BRISTOL 1 STAR
    ,SUM (CASE WHEN Q2.CSISeqNo = 94 AND A.Description = '1 Star'
    OR Q2.CSISeqNo = 107 AND A.Description = '1 Star'
    OR Q2.CSISeqNo = 95 AND A.Description = '1 Star'
    OR Q2.CSISeqNo = 96 AND A.Description = '1 Star'

THEN 1 ELSE 0 END ) AS [1 Star]
FROM CSIQuestionsLog Q
JOIN CSIQuestions Q2 on Q.CSISeqNo=Q2.CSISeqNo
JOIN LogFile L on Q.LogSeqNo=L.Seq
JOIN CSIAnswers A on A.CSISeqNo=Q2.CSISeqNo AND A.AnswerId=Q.Answer
WHERE Q.Created >='2017-10-01'AND Q.Created <'2017-11-01'
AND Q.Dealer IN ('BA')
GROUP BY Q.Dealer, Q2.Question, Q.CSISeqNo
ORDER BY Q.Dealer, Q.CSISeqNo

This is a total shot in the dark.
SELECT Q.Dealer ,Q.CSISeqNo ,Q2.Question --BRISTOL 1 STAR ,SUM (CASE WHEN Q2.CSISeqNo IN( 94, 95, 96,107) AND A.Description = '1 Star' THEN 1 ELSE 0 END ) AS [1 Star]

FROM CSIQuestionsLog Q JOIN CSIQuestions Q2 on Q.CSISeqNo=Q2.CSISeqNo JOIN LogFile L on Q.LogSeqNo=L.Seq JOIN CSIAnswers A on A.CSISeqNo=Q2.CSISeqNo AND A.AnswerId=Q.Answer

WHERE Q.Created >='2017-10-01'AND Q.Created <'2017-11-01' AND Q.Dealer IN ('BA') GROUP BY Q.Dealer, Q2.Question, Q.CSISeqNo, Q.REGNO -- <<---Maybe this? ORDER BY Q.Dealer, Q.CSISeqNo

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

craig.jenkins SSCommitted Points: 1643 More actions · Answer 2

Thanks for the help but still getting duplicates. Appreciate it tho

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

craig.jenkins - Friday, November 17, 2017 8:24 AM
Thanks for the help but still getting duplicates. Appreciate it tho

What do you mean by duplicates? What does your data look like? What are you trying to achieve? Read the article in my signature to understand what you can post that would be helpful for us to give you relevant advice.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 4

craig.jenkins - Friday, November 17, 2017 6:36 AM

Did you read the rules of this forum? Where is the DDL you didn't post? Would you like to try again and actually followed the basic netiquette?

It would also help if you know something about naming data elements. While it's dull and boring, it would be worth it to read any book on basic data modeling and see what the ISO 11179 naming rules are. For example, there is no such thing as a generic "description"; it has to be the description of something in particular. Likewise, what attribute of the dealer are we looking at? I would guess it's an identifier but if you don't tell us where just like your compiler: we don't know. We have no idea about keys, REFERENCES, etc.

Hre is a clean-up of your posting.

SELECT Q.dealer_id , Q.csi_seq, Q2.question_nbr,
   SUM (CASE WHEN Q2.csi_seq IN (94, 95, 96, 107
        AND A.answer_txt = '1 Star'
    THEN 1 ELSE 0 END) AS one_star_tot
FROM CSI_QuestionsLog AS Q,
   CSI_Questions AS Q2,
   LogFile AS L,
   CSI_Answers AS A
WHERE Q.creation_date BETWEEN '2017-10-01'AND '2017-11-01'
AND Q.dealer_id = 'BA'
AND Q.log_seq = L.log_seq
AND A.csi_seq = Q2.csi_seq
AND A.answer_id = Q.answer_id
GROUP BY Q.dealer_id, Q2.question_nbr, Q.csi_seq;

Unlike Luis, I do not feel like shooting in the dark.

Please post DDL and follow ANSI/ISO standards when asking for help.