Finding count in a resultset

Question

Post reply

Finding count in a resultset

deastr

SSC Enthusiast

Points: 111
More actions
June 19, 2008 at 6:28 am

#187312

I have a select with a few joins producing this results table:
1 Name1 Date1
1 Name1 Date1
1 Name1 Date1
2 Name1 Date2
2 Name1 Date2
2 Name1 Date2
2 Name1 Date2
How can I turn it to this:
1 Name1 Date1 Count3
2 Name1 Date2 Count4

Viewing 8 posts - 1 through 8 (of 8 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Michael Earl-395764

SSC Guru

Points: 53873

June 19, 2008 at 7:17 am

#831080

SELECT Col1, Col2, COUNT(*)

FROM X

GROUP BY Col1, Col2

deastr SSC Enthusiast Points: 111 More actions · Answer 2

Michael Earl (6/19/2008)
SELECT Col1, Col2, COUNT(*)
FROM X
GROUP BY Col1, Col2

Thanks but this just adds "1" column to the results, like:

1 Name1 1

2 Name1 1

2 Name2 1

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 3

[font="Verdana"]Solution given by Michael is correct. You must be missing something. Check it out.

Mahesh[/font]

MH-09-AM-8694

deastr SSC Enthusiast Points: 111 More actions · Answer 4

I was obviously not clear about my question, I'm sorry about that. Here's a more complete data about what I'd like to achieve:

There're two tables. It's about tests created by users and the questions added by them for each tests. The main tests table:

[TableTests]

TestID UserID

--------------

1 25

2 25

[TableQuestions]

TestID QuestionID

------------------

1200

1201

1202

1203

2200

2205

What I want to display to user with a query is this:

"User 25, you have following tests:

Test number 1, has 4 questions

Test number 2, has 2 questions"

I joined tables with inner join and had the results in my original post and thought if there's a way to achieve this modifying my query in any way. I'm not so good with joins so I can't think any other way to do this with a single query or in a single stored procedure. I appreciate any help..

dolfa SSC Journeyman Points: 82 More actions · Answer 5

Hi

Try this

select t1.userid,t2.testid,count(t2.questionid)

from #table1 t1

left join #table2 t2 on t1.testid = t2.testid

group by t2.testid,t1.userid

Vladan SSC-Insane Points: 22072 More actions · Answer 6

Basically, this problem is very similar to the one solved here:

http://www.sqlservercentral.com/Forums/Topic520209-338-1.aspx

You can get the result you need with the following query:

SELECT t.UserID, t.TestID, Q.Questions

FROM Tests t

JOIN (SELECT TestID, COUNT(*) as Questions

FROM Questions

GROUP BY TestID) as Q ON Q.TestID = t.TestID

Avaneesh -388582 SSChasing Mays Points: 657 More actions · Answer 7

Hi I went on to add one more record to the table 'TableTest' as under:

TestID UserID

1 26

and wrote the following query:

select tt.userid,tq.testid, count(tq.testID) 'total questions' from tablequestions tq

INNER JOIN tabletest tt

ON tq.testid = tt.testid

group by tq.testid,tt.userid

It fetched me following records:

UserID TestID Total Questions

2514

2522

2614

Hope that helps,

Regards

Avanesh Bajoria.