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 - 2004-03-08 : 08:26:32
|
| cathie writes "in my query i want to find the amount for each transaction and sum this amount but i only want to return rows where the sum of the amount is greater than 6300 and between two date parameters. here is my query:SELECT sum(dbo.SALFLDGSDE.AMOUNT) AS AMOUNT, dbo.SSRFACC.ACCNT_CODE, dbo.SSRFACC.ACCNT_NAME, dbo.SSRFADD.ADDRESS_2, dbo.SSRFADD.ADDRESS_3, dbo.SSRFADD.ADDRESS_4, dbo.SSRFADD.ADDRESS_5, dbo.SSRFADD.ADDRESS_6, dbo.SSRFACC.LAST_TRANS FROM dbo.SSRFADD INNER JOIN dbo.SSRFACC ON dbo.SSRFADD.ADD_CODE = dbo.SSRFACC.ADD_CODE INNER JOIN dbo.SALFLDGSDE ON dbo.SSRFACC.ACCNT_CODE = dbo.SALFLDGSDE.ACCNT_CODE WHERE (dbo.SSRFACC.SUN_DB = 'SDE') AND (dbo.SSRFADD.SUN_DB = 'SDE') AND (dbo.SALFLDGSDE.TRANS_DATE > @pFromDate) AND (dbo.SALFLDGSDE.TRANS_DATE < @pToDate) AND (dbo.SSRFACC.ACCNT_CODE BETWEEN 'C000000' AND 'C999998') AND (dbo.SALFLDGSDE.JRNAL_TYPE = 'SYSTM') AND(dbo.SALFLDGSDE.ACCNT_CODE = dbo.SSRFACC.ACCNT_CODE) AND (dbo.SSRFADD.ADD_CODE = dbo.SSRFACC.ADD_CODE) GROUP BY dbo.SSRFACC.ACCNT_CODE, dbo.SSRFACC.ACCNT_NAME, dbo.SSRFADD.ADDRESS_2, dbo.SSRFADD.ADDRESS_3, dbo.SSRFADD.ADDRESS_4, dbo.SSRFADD.ADDRESS_5, dbo.SSRFADD.ADDRESS_6, dbo.SSRFACC.LAST_TRANS HAVING sum (dbo.SALFLDGSDE.AMOUNT) > 6300 order by dbo.SSRFACC.ACCNT_CODEno rows are returned when i run this query. when i add dbo.SALFLDGSDE.AMOUNT to the GROUP BY all rows are returned and the amount is not summed so multiple rows for one account number are returned. what i want returned is the sum of the amount for each account code where the amount is greater then 6300.any suggestions??" |
|
|
ChrisFretwell
Starting Member
43 Posts |
Posted - 2004-03-08 : 12:05:58
|
| My best suggestion is to step back and start with a single table, in this case salfldgsde and get the sum, group by and having working the way you want. Then add the other tables one at a time.If it gets too messy then use a subquery that sums by account and then join that to the rest of your tables. At the surface, without looking at data, you're query looks okay. Are you sure there is data (within the date ranges specified) that have sums over 6300?Chris |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-03-08 : 14:07:32
|
| if you are grouping by Address1, Address2, etc, then you know your SQL is probably not written in a clean way. As Chris suggests, break it up into smaller pieces and calculate your SUM() in a derived table, and then join it all together at the end. Test out each piece, one at a time, and put it together piece by piece, and so on....- Jeff |
|
|
|
|
|
|
|
|
aggregate function sum - not adding