|
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
SQL Stored Prodcedure Group by and Sum clauses
I have a SQL stored procedure that is returning results in a table as follows (Below): -
I would like to have the results grouped by price so as it only appears the once. I would like to have it so as the copies and Rev totals for each price are summed up. To show exactly what I mean please look at the revised table below: No publication_name calculated_price Copies Rev 1 Belfast Telegraph 0.01 1549 15.49 1 Belfast Telegraph 0.01 149 1.49 1 Belfast Telegraph 0.375 16578 6216.75 1 Belfast Telegraph 0.375 1578 986.75 1 Belfast Telegraph 0.375 9578 2245.75 1 Belfast Telegraph 0.375 18578 6546.75 1 Belfast Telegraph 0.41 8530 3497.3 1 Belfast Telegraph 0.41 8530 3497.3 1 Belfast Telegraph 0.41 8530 3497.3 1 Belfast Telegraph 0.5 9854 4927 1 Belfast Telegraph 0.5 9854 4927 1 Belfast Telegraph 0.5 9854 4927 2 Sunday Life 0.25 547 136.75 2 Sunday Life 0.25 156 39.5 2 Sunday Life 0.25 156 39.5 2 Sunday Life 0.76 68 14.58 2 Sunday Life 0.76 3568 2711.68 2 Sunday Life 0.61 1689 1030.29 2 Sunday Life 0.61 1689 1030.29 2 Sunday Life 0.61 1689 1030.29 2 Sunday Life 0.76 3568 2711.68 2 Sunday Life 0.76 3568 2711.68 I would like the results to come back as follows: - 1 Belfast Telegraph 0.01 1849 18.49 1 Belfast Telegraph 0.375 46312 17637 1 Belfast Telegraph 0.41 8530 3497.3 1 Belfast Telegraph 0.5 9854 4927 2 Sunday Life 0.25 703 175.75 2 Sunday Life 0.76 7204 5475.04 2 Sunday Life 0.61 1689 1030.29 My stored procedure at present is as follows: - SELECT REPORT_WKENDING.No, REPORT_WKENDING.publication_name, REPORT_WKENDING.calculated_price, REPORT_WKENDING.Copies, REPORT_WKENDING.Rev FROM dbo.REPORT_WKENDING order by dbo.REPORT_WKENDING.PubNumber Could someone please help me to re-write this so as it groups the prices together and sums up the Copies and revenue for each price. I would be so grateful for any help anyone is able to give me. Im pretty knew to stored procedures. Thank you |
|
#2
|
|||
|
|||
|
SELECT RW.No, RW.publication_name, RW.calculated_price, Sum(RW.Copies) AS Copies, Sum(RW.Rev) as Rev
FROM (SELECT Distinct REPORT_WKENDING.No, REPORT_WKENDING.publication_name, REPORT_WKENDING.calculated_price, REPORT_WKENDING.Copies, REPORT_WKENDING.Rev FROM dbo.REPORT_WKENDING) AS RW Group By RW.No, RW.publication_name, RW.calculated_price Syntax may be slightly off, but you should get the Idea S- |
![]() |
| Viewing: ASP Free Forums > Database > SQL Development > SQL Stored Prodcedure Group by and Sum clauses |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|