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
精彩评论