Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
solisjojo
Starting Member
4 Posts |
 Posted - 2005-05-23 : 03:26:52
|
| Hi Experts,I need your help for this SQL Query, Please take a look at the provided sample data and my preffered results. With this samples what i need is to get the totals for each magazines titles and i want the results to be sorted by TotalCost. What i did to get the results is I run a Query first to get the Distinct Title and for each title i run a second query to get all the totals, but the problem is i cannot sort the results base on the TotalCost. Please helpThanks!Sample DataTitle | InsertionCost | Terms--------------------------------------------MAG1 | 50000 | CASHMAG1 | 40000 | EXDEALMAG1 | 30000 | CASHMAG2 | 40000 | CASHMAG2 | 23000 | CASHMAG3 | 17000 | EXDEALMAG3 | 23000 | CASHMAG3 | 25000 | EXDEALPreffered Results:Title | CASH | EXDEAL | TOTALCost--------------------------------------MAG1 80000 40000 120000MAG3 23000 42000 65000MAG2 63000 0 63000 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2005-05-23 : 03:45:11
|
To sort by a computed column or expression, you can use a neat trick called Derived tables. They can be useful in other scenarios as well, but here is how you would use them in your case:SELECT Col1, Col2, Col3 FROM( SELECT Col1, Col2, SUM(SomeComplexExpression) AS Col3 FROM Table1 GROUP BY Col1, Col2) AS XORDER BY Col3 DESC Btw, what you are trying to accomplish in your original query is a cross-tab or matrix. Search this site for cross-tab and you will get plenty of hints on how to do it efficiently.OS |
 |
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2005-05-23 : 04:08:23
|
There is no need for a derived tableSELECT title, sum(case when terms = 'CASH' then InsertionCost else 0 end) as CASH, sum(case when terms = 'EXDEAL' then InsertionCost else 0 end) as EXDEAL, SUM(insertionCost) AS total FROM T GROUP BY title order by total desc |
|
|
|
solisjojo
Starting Member
4 Posts |
Posted - 2005-05-23 : 20:22:00
|
| Hi LarsG,Your code works perfectly! thanks for your quick response.Jojo Solis |
|
|
|
|
|
|
|
|
help with Sorted Query