Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sum these rows

Posted on 2013-01-27
1
Medium Priority
?
326 Views
Last Modified: 2013-01-27
I have the SQL statement below and a table. I want to sum up the "totalprice" column and show one row. As it is now...2 rows show up.

This is sample of data:

 SELECT  
             businessnameId = CAST(sd.Id AS VARCHAR(MAX)),
          NAME = sd.BusinessName,
          TotalNumOfHCPs =(SELECT COUNT(*) FROM dbo.SignupHCProvider WHERE BusinessNameId = sd.id) ,
          NumPaidHCPs = (SELECT COUNT(*) FROM dbo.PaymentSubscriptionLog WHERE BusinessNameId = sd.id) ,
          UnpaidHCPs = (SELECT COUNT(*) FROM dbo.SignupHCProvider WHERE BusinessNameId = sd.id) - 
                    (SELECT COUNT(*) FROM dbo.PaymentSubscriptionLog WHERE BusinessNameId = sd.id),
       
          website= website, 
          datecreate= CreateDate,
           isnull(p.TotalPrice,10) AS price
           FROM dbo.SignupDescription sd
         LEFT JOIN dbo.OfficePrice p ON p.BusinessNameId = sd.Id
 WHERE sd.Id = 478 -- added for testing to show you how the data looks like
  ORDER BY NAME 

Open in new window


OfficePrice table

BusinessnameID              discount    price          totalPrice
478                                     20               100                  80      
478                                      10               100                  90      

SQL Above brings back 2 rows because i have 2 rows in OfficePrice. But i want one row to come back with totalPrice column added as 170

BusinessName Id               Name           TotalNumOfHCPs ........ price
478                                         xyz                        2                              170
0
Comment
Question by:Camillia
[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
1 Comment
 
LVL 4

Accepted Solution

by:
mcmahon_s earned 2000 total points
ID: 38825423
Just replace:

LEFT JOIN dbo.OfficePrice p ON p.BusinessNameId = sd.Id

with

LEFT JOIN (SELECT BusinessNameID, SUM(TotalPrice) TotalPrice FROM dbo.OfficePrice GROUP BY BusinessNameID) p ON p.BusinessNameId = sd.Id
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Detach & Attach 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.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

636 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