asked on

SQL Server - Group By

This query is returning the same amount of records every time. Why?

SELECT c.CategoryName, Count(c.CatID) as countedrow
FROM (Questions Q
INNER JOIN Category C
on Q.CategoryID = Q.CategoryID)
Group By C.CategoryName

plusone3055

beucause its grouping the results by category name

try this...

SELECT c.CategoryName, Count(c.CatID) as countedrow
FROM (Questions Q
INNER JOIN Category C
on Q.CategoryID = Q.CategoryID)

without the group by clause you should get different results

vbnetcoder

ASKER

I want it to return the count per category

plusone3055

can you post whats its cirrently returning in a screenshot please.

forgive me
need to see what its reutrning visually to help you better :)

vbnetcoder

ASKER

I can't show you my data but this is the idea

CategoryName countedrow

Bugs 761
Turtles 761
Bears 761

Notice, that in all cases the count is coming out the same.

plusone3055

you dont need to post the data but can you post the tables so I can write a better query for you

vbnetcoder

ASKER

There are two tables

Question
Category

plusone3055

yes but need to see all the columns in both tables :)

vbnetcoder

ASKER

tables.png

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbnetcoder

ASKER

Thank you. Stupid join error on my part that i was not seeing. Duh!

Dale Fye

How about:

SELECT C.CategoryName, Count(Q.QuestionID) as CatQuestions
FROM Category C
LEFT JOIN Questions Q
ON C.CatID = Q.CatID
Group By C.CategoryName

I'm not really sure you have a QuestionID, but you definately don't want to count the C.CatID