Calculating Subtotals from SQL Query

I have a query which returns some rows. Its column names are like: Age, Gender, DOB etc. What I have to do is, to check how many rows are coming from DB of which Age.

e.g. See the image:

See the age subtotal, it means my query is returning 54 rows of age 0, 1 row of age 1 and so on.

This table of subtotal must display only those ages that are returned by the query. For example there is no Age 2 as it was not returned by query.

How can I sort this issue, if anyone has idea please share. Any help would be highly appreciated.

Thanks in advance.

EDIT: Here is my Stored Procedure:

ALTER PROCEDURE [dbo].[spGetClients]
(
    @orderBy varchar(50),
    @startAge int,
    @endAge int,
    @sex varchar(5),
    @developmentalDisability varchar(200),
    @staffId nvarchar(1024), 
    @statusId nvarchar(1024), 
    @ethnicityId nvarchar(1024), 
    @treatmentProviderId nvarchar(1024)
)

AS
BEGIN
    SET NOCOUNT ON;

        SELECT c.Id, dbo.GetClientFullName(c.FirstName, c.MiddleInit, c.LastName) AS ClientName, 
        c.DateOfBirth, dbo.GetAge(c.DateOfBirth, GETDATE()) AS Age, c.Sex, 
        dbo.GetClientStatus(c.Id, @statusId) AS Status, ca.Address, co.Phone, 
        dbo.GetEthnicity(c.Id, @ethnicityId) AS Ethnicity, dbo.GetDevelopmentalDisabilities(c.Id, @developmentalDisability) AS Disabilities, 
        dbo.GetClientStaffContacts(c.Id, @staffId) AS Staff, dbo.GetClientContacts(c.Id) AS Contact, 
        dbo.GetClientInsuranceProviders(c.Id) AS HealthProvider, 
        dbo.GetClientTreatmentProviders(c.Id, @treatmentProviderId) AS TreatmentProvider
        FROM Client c  
        LEFT OUTER JOIN(        
            SELECT ca.ParentEntityId, ca.Address
            FROM ContactAddress ca
            INNER JOIN EntityName en ON en.Id = ca.EntityNameId AND en.Name = 'Client' 
            INNER JOIN GeneralLookup gl ON ca.glAddressTypeId = gl.Id AND gl.LookupItem = 'Primary'    
        ) ca ON c.Id = ca.ParentEntityId 
        LEFT OUTER JOIN(        
            SELECT co.ParentEntityId, co.ContactData Phone
            FROM ContactOther co
            INNER JOIN EntityName en ON en.Id = co.EntityNameId AND en.Name = 'Client' 
            INNER JOIN GeneralLookup gl ON co.glContactTypeId = gl.Id AND gl.LookupItem = 'Home'    
        ) co ON c.Id = co.ParentEntityId 
        LEFT OUTER JOIN GeneralStatus gs on gs.Id = c.StatusId
        where gs.Name <> 'Deleted' 
        and (dbo.GetAge(c.DateOfBirth, GETDATE()) BETWEEN @startAge and @endAge)
        and ((@sex = 'M' and c.sex = 'M') or (@sex = 'F' and c.Sex = 'F') or (@sex = 'Both' and (c.Sex in ('M', 'F', ''))))
        and ((@staffId = '') OR (dbo.GetClientStaffContacts(c.Id, @staffId) is not null))
        and ((@statusId = '') OR (dbo.GetClientStatus(c.Id, @statusId) is not null))
        and ((@ethnicityId = '') OR (dbo.GetEthnicity(c.Id, @ethnicityId) is not null))
        and ((@treatmentProviderId = '') OR (dbo.GetClientTreatmentProviders(c.Id, @treatmentProviderId) is not n开发者_运维技巧ull))
        ORDER BY 
            CASE
                WHEN @orderBy = 'Consumer Name' THEN dbo.GetClientFullName(c.FirstName, c.MiddleInit, c.LastName) 
                WHEN @orderBy = 'Consumer Address' THEN ca.Address
            END
END

SELECT Age, count(Age) SubTotals
  FROM (sub query)
 GROUP BY Age

grouping by Age, the query will show Age and count(Age) = number of rows with that age

Update:

You can do this:

SELECT T.*, TA.Age_SubTotals
FROM (YOUR QUERY) T
JOIN (SELECT dbo.GetAge(c.DateOfBirth, GETDATE()) AS Age, count(*) Age_SubTotals
        FROM Client c
       GROUP BY Age) TA
     ON T.Age = TA.Age

This solution is not efficient at all (and I see that you will need to calculate many other subtotals). It's better to leave the proc as it is and do the counting of subtotals in your client application.

Your query is pretty elaborate and the output seems to be for a report, couldn't you just elaborate your sub-total in the report? (SSRS Tutorial for groups and totals)

If it is not possible, i think you could modify your stored procedure to use a Table Variable: load the query in the table and then run the various subtotal query on it.

Without knowing your struture is going to be something like

SELECT count(age),age FROM `table` WHERE count(age) > 0 GROUP BY age ORDER BY age ASC