SQL SERVER SIMPLE SELECT QUERY NEEDED

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
Subbu GAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SharathConnect With a Mentor Data EngineerCommented:
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
 
SharathData EngineerCommented:
I don't understand. Can you explain what columns you already have and what/how do you want to derive?
0
 
Brian PringleSystems Analyst II, SCM, ERPCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
Subbu GAuthor Commented:
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
 
Subbu GAuthor Commented:
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
 
Brian PringleSystems Analyst II, SCM, ERPCommented:
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
 
Brian PringleConnect With a Mentor Systems Analyst II, SCM, ERPCommented:
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
All Courses

From novice to tech pro — start learning today.