Could anyone help me on this query
-
Hi All,
I have a table which contains the following fields:
RightRezLog_id,ProductCode,FilterDesc,FilteredCount, RowsCount
Here R_id is the primary key
The table is somewhat like this:
R_id ProductCode FilterDesc RowsCount FilterCount
99 APUS After all non HK removed 6 0
12 APUS After all non HK removed 6 0
32 APUS After all non HK removed 6 0
82 APUS After all non HK removed 6 0
56 APUS After all non HK removed 6 0
78 APUS After Groupname and POT flights 7 0
89 APUS After Groupname and POT flights 7 0
47 APUS After Groupname and POT flights 7 0
98 APUS After Groupname and POT flights 7 0
87 APUS All the Records for DPD 16 0
97 APUS All the Records for DPD 16 0
57 APUS All the Records for DPD 16 0
81 APUS All the Records for DPD 16 0
I want to retrieve all the rows with the sum of the rowcount based on the FilterDesc along with the respective R_id
For ex:For FilterDesc After all non HK Removed the Rowcount should be as 30
Thanks in advance
How can this be done?
-
here's how i would do it:
--sample data for testing:
DECLARE @SAMPLE TABLE(R_id int, ProductCode varchar(4), FilterDesc varchar(50), RowsCount int, FilterCount int)
INSERT INTO @SAMPLE
SELECT 99 ,'APUS','After all non HK removed', 6, 0 UNION
SELECT 12 ,'APUS','After all non HK removed',6, 0 UNION
SELECT 32 ,'APUS','After all non HK removed',6, 0 UNION
SELECT 82 ,'APUS','After all non HK removed',6, 0 UNION
SELECT 56 ,'APUS','After all non HK removed',6, 0 UNION
SELECT 78 ,'APUS','After Groupname and POT flights',7, 0 UNION
SELECT 89 ,'APUS','After Groupname and POT flights',7, 0 UNION
SELECT 47 ,'APUS','After Groupname and POT flights',7, 0 UNION
SELECT 98 ,'APUS','After Groupname and POT flights',7, 0 UNION
SELECT 87 ,'APUS','All the Records for DPD',16, 0 UNION
SELECT 97 ,'APUS','All the Records for DPD',16, 0 UNION
SELECT 57 ,'APUS','All the Records for DPD',16, 0 UNION
SELECT 81 ,'APUS','All the Records for DPD',16, 0
-- the sum of the rowcount based on the FilterDesc along with the respective R_id
SELECT SUM(RowsCount)AS SUMROWCOUNT ,FilterDesc
FROM @SAMPLE
GROUP BY FilterDesc
RESULTS:
SUMROWCOUNT FilterDesc
----------- --------------------------------------------------
30 After all non HK removed
28 After Groupname and POT flights
64 All the Records for DPD
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
But i want the R_id also to be displayed.How can that be done...
I used stored procedure but it is not working properly
-
which RID? if you group on R_ID AND FilterDesc, you get the whole table...
SELECT SUM(RowsCount)AS SUMROWCOUNT ,FilterDesc
FROM @SAMPLE
GROUP BY FilterDesc
maybe you mean you want the r_ID ANd the totals?
select S.R_ID,X.SUMROWCOUNT ,X.FilterDesc
FROM @SAMPLE S
INNER JOIN (SELECT SUM(RowsCount)AS SUMROWCOUNT ,FilterDesc
FROM @SAMPLE
GROUP BY FilterDesc) X ON S.FilterDesc = X.FilterDesc
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Here's another way
SELECT R_id,ProductCode,FilterDesc,RowsCount,FilterCount,
SUM(RowsCount) OVER(PARTITION BY FilterDesc) AS SUMROWCOUNT
FROM @SAMPLE
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply