Solved

SQL SERVER SIMPLE SELECT QUERY NEEDED

Posted on 2016-08-16
7
41 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 40

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 40

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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, …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

778 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