Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL SERVER SIMPLE SELECT QUERY NEEDED

Posted on 2016-08-16
7
Medium Priority
?
67 Views
Last Modified: 2016-08-17
Is there a way I can write a select query that gives me just two total as given in the picture
For one ProjectContractid, I want to  add 2 total amount for each 'ChangeOrderNumber' and only 1 distinct 'InitialtotalasBid' value      

Sample
0
Comment
Question by:Subbu G
  • 3
  • 2
  • 2
7 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 41758499
I don't understand. Can you explain what columns you already have and what/how do you want to derive?
0
 
LVL 16

Expert Comment

by:Brian Pringle
ID: 41758500
You could do something like the following...

select ProjectNumber, ApplicantName, ContractorName, MIN(InitialTotalAsBid), SUM(TotalAmount)
from %tablename%
order by ProjectNumber, ApplicantName, ContractorName

Open in new window

0
 

Author Comment

by:Subbu G
ID: 41758513
MIN(InitialTotalAsBid  - this part I dont understand. It does not work for me.

It just gives me 3 as a value.  I wanted to have 13003 as per my example
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Subbu G
ID: 41758517
SELECT
                    SUM(InitialTotalAsBid) AS GrandInitialTotal,
                    SUM(InitialAllowableAsBid) AS GrandInitialAllowableAsBid,
                    SUM(TotalAmount)  AS GrandTotalAmount,
                    SUM(AllowableAmount) As GrandAllowableAmount
                         FROM @ChangeOrderSummary AS ChangeOrderSummary
                        --order by ProjectNumber, ApplicantName, ContractorName

GrandInitialTotal      GrandInitialAllowableAsBid      GrandTotalAmount      GrandAllowableAmount
26006.00            68688.00                                                  55757.00                         44999.00

I wanted
GrandInitialTotal      GrandInitialAllowableAsBid      GrandTotalAmount      GrandAllowableAmount
13003                      34341                                                 55757.00                         44999.00
0
 
LVL 16

Expert Comment

by:Brian Pringle
ID: 41758537
Sorry... I just realized that I had "order by" instead of "group by" in the above code.  I just tried this and it is working.

select ProjectNumber, ApplicantName, ContractorName, MIN(InitialTotalAsBid), SUM(TotalAmount)
from %tablename%
order by ProjectNumber, ApplicantName, ContractorName

Open in new window


(I only added the first 4 lines in mine)

Output
0
 
LVL 16

Assisted Solution

by:Brian Pringle
Brian Pringle earned 1000 total points
ID: 41758542
Wait... you want the grand total?  You could use the GROUP BY ROLLUP function.

group by rollup(ProjectNumber, ApplicantName, ContractorName)

Open in new window

1
 
LVL 41

Accepted Solution

by:
Sharath earned 1000 total points
ID: 41758768
try this.
SELECT SUM(CASE rn WHEN 1 THEN InitialTotalAsBid ELSE 0 END) AS GrandInitialTotal,
       SUM(CASE rn WHEN 1 THEN InitialAllowableAsBid ELSE 0 END) AS GrandInitialAllowableAsBid,
       SUM(TotalAmount) AS GrandTotalAmount,
       SUM(AllowableAmount) As GrandAllowableAmount
  FROM ( SELECT *,
                ROW_NUMBER() OVER(PARTITION BY ProjectNumber, ProjectContractID ORDER BY ProjectContractID) rn
           FROM @ChangeOrderSummary) t1

Open in new window

1

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question