Hi All, I have this following table PK Part revision date year Type task1 activity amount deleteDate Notes 1 CA BASIC 12/23/2010 2010 T TBD travel 4000 TestNotes1 2 CA BASIC 12/23/2010 2010 T TBD1 travel1 5000 TestNotse2 3 CA BASIC 12/23/2010 2010 T TBD1 travel2 5000 TestNotse3 4 CA BASIC 12/23/2010 2010 A TBA travel3 6000 TestNotes4 5 CA BASIC 12/23/2010 2010 A TBA travel4 6000 TestNotes5 6 CA BASIC 12/23/2010 2010 A TBA travel5 6000 TestNotes6 7 CA BASIC 12/23/2010 2010 A TBA travel6 6000 TestNotes7 8 CA BASIC 12/23/2010 2012 A TBA travel5 6000 TestNotes8 9 CA BASIC 12/23/2010 2012 A TBA travel100 6000 TestNotes9 10 CA BASIC 12/23/2010 2012 T TBA travel10 6000 TestNotes 10 11 CA BASIC 12/23/2010 2012 T TBA travel9 6000 TestNotes11
I want to produce the output like this with sql:Part revision date year Type task1 activity amount deleteDate NotesCA BASIC 12/23/2010 2010 T TBD travel 4000 TestNotes1CA BASIC 12/23/2010 2010 T TBD1 travel1 5000 TestNotse2CA BASIC 12/23/2010 2010 T TBD1 travel2 5000 TestNotes -------- 14000CA BASIC 12/23/2010 2010 A TBA travel3 6000 TestNotes4CA BASIC 12/23/2010 2010 A TBA travel4 6000 TestNotes5CA BASIC 12/23/2010 2010 A TBA travel5 6000 TestNotes6CA BASIC 12/23/2010 2010 A TBA travel6 6000 TestNotes7CA BASIC 12/23/2010 2012 A TBA travel5 6000 TestNotes8CA BASIC 12/23/2010 2012 A TBA travel100 6000 TestNotes9 ----- 36000
so basically for one particular part, when the type changes then I want to calculate sum and when the year changes then I want to get another output Part revision date year Type task1 activity amount deleteDate Notes CA BASIC 12/23/2010 2012 A TBA travel5 6000 TestNotes8 CA BASIC 12/23/2010 2012 A TBA travel100 6000 TestNotes9 ------- 12000 CA BASIC 12/23/2010 2012 T TBA travel10 6000 TestNotes 10 CA BASIC 12/23/2010 2012 T TBA travel9 6000 TestNotes11 ------- 12000
I trying to use group by clause, but couldn't figure out to get all the columns with the group by clause.I can create different reports based on the year on C# side, but when the type changes then I want to get the total and then continue with another type. Is it possible to do this in sql.any help will be highly apprecaited.