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 |
|
o9z
Starting Member
23 Posts |
 Posted - 2010-09-22 : 12:23:19
|
| I have a table that has data like this:Material | Pounds------------------VEG | 2500VEG | 1600VEG-DM | 2000CRN | 1200CRN | 800CRN-DM | 1000My SQL statement currently looks like this:SELECT Material, SUM(Pounds) As PoundsFrom vCurrentDateGROUP BY MaterialAnd it returns this:Material | Pounds------------------VEG | 4100VEG-DM | 2000CRN | 2000CRN-DM | 1000What I want it to return is the same thing as above...only VEG and VEG-DM are combined. And CRN and CRN-DM are combined. So it would return 2 values. VEG and CRN.How would I do this? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-22 : 12:53:40
|
| Group By LEFT(Material,3)Assuming every single material has the same form.--Gail ShawSQL Server MVP |
 |
|
|
o9z
Starting Member
23 Posts |
Posted - 2010-09-22 : 12:57:16
|
| Is there any way to do it while retaining the "material" column in the select? I want the 2 columns it returns to be called Veg and CRN. If I leave material in there now it says that material is invalid in the select list because it is not contained in either an aggregate function of group by clause. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-09-22 : 12:59:10
|
| DECLARE @Table TABLE (Material varchar(10),Pounds int)INSERT INTO @TableSELECT 'VEG',2500 UNION SELECT 'VEG',1600 UNION SELECT 'VEG-DM',2000 UNION SELECT 'CRN',1200 UNION SELECT 'CRN',800 UNION SELECT 'CRN-DM',1000 SELECT SUM(CASE WHEN LEFT(Material ,3) = 'VEG' THEN Pounds ELSE 0 END) as Veg , SUM(CASE WHEN LEFT(Material ,3) = 'CRN' THEN Pounds ELSE 0 END) as CRNFROM @table JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|
|
Easy SQL question for you gurus. Sum combined