Solved

SQL SERVER SIMPLE SELECT QUERY NEEDED

Posted on 2016-08-16
7
50 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

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 250 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 250 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

726 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