[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server - Group By

Posted on 2014-08-05
11
Medium Priority
?
366 Views
Last Modified: 2014-08-05
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
0
Comment
Question by:vbnetcoder
11 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 40241326
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
 

Author Comment

by:vbnetcoder
ID: 40241339
I want it to return the count per category
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40241378
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:vbnetcoder
ID: 40241385
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
 
LVL 22

Expert Comment

by:plusone3055
ID: 40241389
you dont need to post the data but can you post the tables so I can write a better query for you
0
 

Author Comment

by:vbnetcoder
ID: 40241393
There are two tables


Question
Category
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40241398
yes but need to see all the columns in both tables :)
0
 

Author Comment

by:vbnetcoder
ID: 40241445
0
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40241465
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
 

Author Closing Comment

by:vbnetcoder
ID: 40241477
Thank you. Stupid join error on my part that i was not seeing. Duh!
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40241479
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question