[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL SERVER SIMPLE SELECT QUERY NEEDED

Posted on 2016-08-16
7
Medium Priority
?
62 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

649 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