To display count using SQL Server stored procedure

Question

In the below query I have a table name indent I have different indentid count for different locations. But it shows the indentid count for last location. I want to display all location indentID count.

I want to display count like this

    15
    20
     1

15 - for first location
20 - second location
1 - third Location

Code:

 DECLARE @LocationID int

 SELECT @LocationID = LocationID FROM Locations

 SELECT COUNT(IndentID) AS OpenedIndent 
 FROM Indent I 
 WHERE POStatusID IN (1,2) 
   AND I.LocationID = @LocationID

It would help if you gave us a data sample of the table being queried. — Matt
– Matt, Commented May 13, 2015 at 7:25
It is problematic to use SELECT @LocationID = LocationID FROM Locations without a where condition because it is not limiting the results to 1 record (you can only assign 1 value to a scalar variable). — Matt
– Matt, Commented May 13, 2015 at 8:15

Robert · Accepted Answer · 2015-05-13 07:24:07Z

1

You need to use group by as below

SELECT COUNT(IndentID) AS OpenedIndent 
FROM Indent I 
WHERE POStatusID IN (1,2) 
GROUP BY I.LocationID

edited May 13, 2015 at 7:24

answered May 13, 2015 at 7:12

Robert

25.8k8 gold badges70 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Moses Over a year ago

Yes I don't want to see indentID .I want to display count of all locations .

Collectives™ on Stack Overflow

To display count using SQL Server stored procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related