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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql query with totals in between

Author  Topic 

anjali5
Posting Yak Master

121 Posts

Posted - 2014-04-11 : 13:25:34
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 Notes

CA BASIC 12/23/2010 2010 T TBD travel 4000 TestNotes1
CA BASIC 12/23/2010 2010 T TBD1 travel1 5000 TestNotse2
CA BASIC 12/23/2010 2010 T TBD1 travel2 5000 TestNotes
--------
14000

CA BASIC 12/23/2010 2010 A TBA travel3 6000 TestNotes4
CA BASIC 12/23/2010 2010 A TBA travel4 6000 TestNotes5
CA BASIC 12/23/2010 2010 A TBA travel5 6000 TestNotes6
CA BASIC 12/23/2010 2010 A TBA travel6 6000 TestNotes7
CA BASIC 12/23/2010 2012 A TBA travel5 6000 TestNotes8
CA 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.

pvegad
Starting Member

2 Posts

Posted - 2014-04-11 : 16:46:23
hello sir,

my question is ..i have two database and i am transfer data between one database table to another database table...

but one database table is primary key or foreign key....so another table
data is not transfer....so how to solve it..

Software Developer
Pritesh Vegad
98256 99852

Software Developer
Mr. Pritesh Vegad
Mo. 9825699852
Go to Top of Page

pvegad
Starting Member

2 Posts

Posted - 2014-04-11 : 18:05:53
hi anjli ......
how are you?

i think this solution ....yes or not reply me query or not to then you set prodec....

select SUM(Amount) as Amount from produce where type = (select Type
from produce where Type = 'T'
group by Type) or type = (select Type from produce where Type = 'A'
group by Type)group by Type

select SUM(Amount) as Amount from produce where year = (select year
from produce where year = 2010 and Type = 'A'
group by year ) or year = (select year
from produce where year = 2012 and Type = 'T'
group by year )group by Type, GROUPING SETS (Type)


Software Developer
Mr. Pritesh Vegad
Mo. 9825699852
Go to Top of Page
   

- Advertisement -