quote:

---

Originally posted by harsh_athalye

select col1, col2, col3, count(*) as count
from 
(
-- your existing query here
)
group by col1, col2, col3

Note: Replace col1, col2, col3 with appropriate column names respectively.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

---

select col1, col2, col3, count(*) as count
from 
(
-- your existing query here
) as d
group by col1, col2, col3

SELECT		Col1,
		Col2,
		COUNT(*) AS [Count]
FROM		(
			SELECT	Col1,
				Col2
			FROM	Table
		) AS d
GROUP BY	Col1,
		Col2
ORDER BY	Col1,
		Col2

quote:

---

Originally posted by Peso

About what?
It is exactly as you posted in your first post.

Maybe you want this?

SELECT		Col1,
		Col2,
		COUNT(*) AS [Count]
FROM		(
			SELECT	Col1,
				Col2
			FROM	Table
		) AS d
GROUP BY	Col1,
		Col2
ORDER BY	Col1,
		Col2

---

E 12°55'05.25"
N 56°04'39.16"

---

quote:

---

Originally posted by kensai

quote:

---

Originally posted by Peso

About what?
It is exactly as you posted in your first post.

Maybe you want this?

SELECT		Col1,
		Col2,
		COUNT(*) AS [Count]
FROM		(
			SELECT	Col1,
				Col2
			FROM	Table
		) AS d
GROUP BY	Col1,
		Col2
ORDER BY	Col1,
		Col2

---

E 12°55'05.25"
N 56°04'39.16"

---

Peso I'm truly grateful for the help but it just not what I'm looking for. This just adds a column with a value of "1" to every row. I just wonder if it's not possible to do it or if I wasn't clear enough with my explanation..

---

DECLARE	@Sample TABLE
	(
		ID INT,
		Col1 VARCHAR(20),
		Col2 VARCHAR(20)
	)

INSERT	@Sample
SELECT	1, 'Name1', 'Date1' UNION ALL
SELECT	1, 'Name1', 'Date1' UNION ALL
SELECT	1, 'Name1', 'Date1' UNION ALL
SELECT	2, 'Name1', 'Date2' UNION ALL
SELECT	2, 'Name1', 'Date2' UNION ALL
SELECT	2, 'Name1', 'Date2' UNION ALL
SELECT	2, 'Name1', 'Date2'

SELECT		Col1,
		Col2,
		COUNT(*) AS [Count],
		'COUNT' + CAST(COUNT(*) AS VARCHAR(12)) AS Yak
FROM		@Sample
GROUP BY	Col1,
		Col2
ORDER BY	Col1,
		Col2

quote:

---

Originally posted by visakh16
You were not clear. You would sound more clear if you show some sample data together with table columns and also output you wish to get from the data.

---

quote:

---

Originally posted by kensai

quote:

---

Originally posted by visakh16
You were not clear. You would sound more clear if you show some sample data together with table columns and also output you wish to get from the data.

---

Ok 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
------------------
1 200
1 201
1 202
1 203
2 200
2 205

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..

---