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
vbnetcoderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
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
0
vbnetcoderAuthor Commented:
I want it to return the count per category
0
plusone3055Commented:
can you post whats its cirrently returning in a screenshot please.

forgive me
need to see what its reutrning visually to help you better :)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

vbnetcoderAuthor Commented:
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.
0
plusone3055Commented:
you dont need to post the data but can you post the tables so I can write a better query for you
0
vbnetcoderAuthor Commented:
There are two tables


Question
Category
0
plusone3055Commented:
yes but need to see all the columns in both tables :)
0
vbnetcoderAuthor Commented:
0
ste5anSenior DeveloperCommented:
As long as there is no row added or deleted in Category and there is no change in the assignement of Question to Category, which leaves one category empty or populates a prior empty category, then it's correct.

But there are some errors in your query. The most important is the wrong JOIN condition. I think you should take a look at these possiblities:

 
DECLARE @Category TABLE
    (
      CategoryID INT ,
      CategoryName VARCHAR(255)
    );

INSERT  INTO @Category
VALUES  ( 1, '1' ),
        ( 2, '2' ),
        ( 3, '3' ),
        ( 4, '4' );
		
DECLARE @Question TABLE
    (
      QuestionID INT ,
      CategoryID INT ,
      QuestionText VARCHAR(255)
    );

INSERT  INTO @Question
VALUES  ( 1, 1, '1?' ),
        ( 2, 1, '2?' ),
        ( 3, 1, '3?' ),
        ( 4, 1, '4?' ),
        ( 5, 2, '5?' ),
        ( 6, 2, '6?' ),
        ( 7, 2, '7?' ),
        ( 8, 3, '8?' ),
        ( 9, 3, '9?' );

SELECT  C.CategoryName ,
        COUNT(C.CategoryID) AS Cnt
FROM    @Question Q
        INNER JOIN @Category C ON C.CategoryID = Q.CategoryID
GROUP BY C.CategoryName;

SELECT  C.CategoryName ,
        COUNT(*) AS Cnt
FROM    @Category C
        LEFT JOIN @Question Q ON C.CategoryID = Q.CategoryID
GROUP BY C.CategoryName;

SELECT  C.CategoryName ,
        COUNT(Q.QuestionID) AS Cnt
FROM    @Category C
        LEFT JOIN @Question Q ON C.CategoryID = Q.CategoryID
GROUP BY C.CategoryName;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
Thank you. Stupid join error on my part that i was not seeing. Duh!
0
Dale FyeCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.