Mark Zukerman Mark Zukerman - 8 days ago 7
SQL Question

Counting the number of values for each row in SQL

I created a function in SQL that counting the number of LocationIDS the function getting location ID and returning how many times that locationID exist in the TABLE.

My issue is when I'm calling that function in my stored procedure - it is displaying it for each row instead of showing one column

Let's say for example in my table:

LocationID NumberofshippingLocation
------------------------------------
48 2
48 2
52 1


Instead of merge the ones that shown twice to one

LocationID NumberofshippingLocation
-------------------------------------
48 2
52 1


Query:

-- Select [dbo].[fnGetNumberOfShippingLocations](52)

ALTER FUNCTION [dbo].[fnGetNumberOfShippingLocations](@ShippingLocaitonID Int)
RETURNS VarChar(50)
AS
BEGIN
Declare @rValue INT

Select @rValue = COUNT(*)
From GSACPeopleBadgeRequest
Where ShippedLocationID = @ShippingLocaitonID

Return @rValue
END


The call from my stored procedure is like this:

[dbo].[fnGetNumberOfShippingLocations](PBR.ShippedLocationID) AS 'NumberOfShippingLocation'

Answer

You must use a GROUP BY clause.

This counts the number of records per ShippedLocationID (result = 2, for id=48):

SELECT
    @rValue = COUNT(*) 
FROM
    GSACPeopleBadgeRequest
GROUP BY
    ShippedLocationID 
WHERE
    ShippedLocationID = @ShippingLocationID

If you need the sum instead of just the number of records, do this (result = 4, for id=48):

SELECT
    @rValue = SUM(NumberofshippingLocation)
FROM
    GSACPeopleBadgeRequest
GROUP BY
    ShippedLocationID 
WHERE
    ShippedLocationID = @ShippingLocationID
Comments