Multiple Between Statements
I'm a student developer currently taking a SQL course. I'm currently trying to retrieve the sum of several groups of rows from a table. I'm very new to SQL development and I'm not too sure what the problem is in the following scenario. I have a large table of which I want to select specific rows from and then add the balance column.
The following will not display any data in the results and I'm not too sure why. I have a hunch that the between statement can only be used once per table but maybe I'm wrong?
Select Description, SUM(Balance) AS Total_Balance
FROM Chart_Of_Accounts
WHERE (Account BETWEEN ('400401') AND ('400407'))
AND (Account BETWEEN ('440094') AND ('440100'))
AND (Account BETWEEN ('450094') AND ('450100'))
GROUP BY Description, Balance
Edit 10.1.2012 As requested by (Richard aka cyberkiwi)
What I Have
Set 1
|Account |Description |Balance
|4004xx |Red Wine |$2361.23
|4004xx |White Wine |$3620.23
|4004xx |Rice Wine |$1223.23
Set 2
|Account |Description |Balance
|4400xx |Red Wine |$4361.23
|4400xx |White Wine |$3260.23
|4400xx |Rice Wine |$223.23
Set 3
|Account |Description |Balance
|4500xx |Red Wine |$1361.23
|4500xx |White Wine |$1620.23
|4500xx |Rice Wine |$1223.23
Result I'm Trying to Achieve
Total
|Description |Total_Balance
|Red Wine |$8083.69
|White Wine |$8500.69
|Rice Wine |$2669.69
thank you for reading my post! I am also open to any advice in SQL development and any feedback is greatly appreciated.
Answer
Use ORs instead of ANDs!
What you mean to express is, give me results where it is between A and B, or it is between C and D etc.
SELECT Description, SUM(Balance) AS Total_Balance
FROM Chart_Of_Accounts
WHERE (Account BETWEEN '400401' AND '400407')
OR (Account BETWEEN '440094' AND '440100')
OR (Account BETWEEN '450094' AND '450100')
GROUP BY Description
I dropped the extraneous brackets, but you could even drop the remaining ones becaues the ORs get processed after the ANDs. See: SQL Server Operator Precedence