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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-11-09 : 08:20:16
|
| neo writes "hi i am a beginner at sqlthis query confuses me...q:A LIST OF SERVICE TRANSACTIONS(WITH A FULL DESCRIPTION OF THE SERVICE) ON A PARTICULAR DAY ALONG WITH THE NAMES OF THE CUSTOMER AND THEIR ROOM NUMBERS.THE TOTAL AMOUNT TRANSACTED NEEDS TO BE DISPLAYED AT THE BOTTOM OF THE LISTREQD TABLES ARE SERVICES,TRANSACTIONS AND RESERVATIONS...all i can do was this answer...SELECT T.TRANID,S.*,R.FNAME,R.LNAME,R.ROOMID FROM SERVICES AS S ,RESERVATIONS AS R,TRANSACTIONS AS T WHERE S.SERNO=T.SERNO AND T.CUSTID=R.CUSTID ORDER BY T.TRANIDCOMPUTE SUM(S.SERCHAR)is there a better way to display in the same table the sum too?" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-11-09 : 09:59:52
|
| look up WITH ROLLUPJay White |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-09 : 10:22:46
|
| WITH ROLLUP requires that you be grouping which in this case will get tricky.You can just use a UNION to add the extra row, put NULL or a literal value in all the other columns. Something likeSELECT T.TRANID,S.*,R.FNAME,R.LNAME,R.ROOMID FROM SERVICES AS S ,RESERVATIONS AS R,TRANSACTIONS AS T WHERE S.SERNO=T.SERNO AND T.CUSTID=R.CUSTID ORDER BY T.TRANIDUNION ALLSELECT NULL, NULL, NULL, SUM(S.SERCHAR), NULL, NULL, NULLFROM SERVICES AS S ,RESERVATIONS AS R,TRANSACTIONS AS T WHERE S.SERNO=T.SERNO AND T.CUSTID=R.CUSTID ORDER BY T.TRANID |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-11-09 : 12:02:26
|
| You could group by the entire rowsetJay White |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-09 : 12:59:54
|
quote:
Originally posted by Page47
You could group by the entire rowsetJay White
Yeah, that's what I defined as will get tricky  |
|
|
|
|
|
|
|
|
displaying sum at the bottom of the table