Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem getting distinct value along with sums SQL Server

Posted on 2016-09-22
12
Medium Priority
?
82 Views
Last Modified: 2016-10-07
Let's say I run the following query:

SELECT        TOP (100) PERCENT Job, Total_Price, Invoice_Amount AS pri, hrs2 AS hrs, material3 AS mat, service2 AS ser, labor2 AS lab
FROM            dbo.V_Cust_Cost_Det_Inv_Date_B
WHERE        (Invoice_Date BETWEEN '9/1/16' AND '9/21/16')
ORDER BY Total_Price

Open in new window


It returns something like this:

Job / Total_Price / pri / hrs / mat / ser / lab
104591      5249.9900      4749.9900      26.589133      2078.7292      0.0000      439.6842
104591      5249.9900      500.0000      2.797928      218.7410      0.0000      46.2672

What I want is to be able to query the view and get sums of pri / hrs / mat / ser / lab but only the distinct value of Total_Price. I don't want them to sum. So far I have this which does not include the Total_Price:

SELECT        SUM(Invoice_Amount) AS pri, SUM(hrs2) AS hrs, SUM(material3) AS mat, SUM(service2) AS ser, SUM(labor2) AS lab
FROM            dbo.V_Cust_Cost_Det_Inv_Date_B
WHERE        (Invoice_Date BETWEEN '9/1/16' AND '9/21/16')

Open in new window


If somehow I could incorporate something like this into the query:

SELECT SUM(tpri) AS tpri FROM (SELECT DISTINCT Job, Total_Price AS tpri FROM V_Cust_Cost_Det_Inv_Date_B WHERE (Invoice_Date BETWEEN '9/1/16' AND '9/21/16')) AS derivedtbl_1

Open in new window


In the end this is what I want to see:

Job / Total_Price / pri / hrs / mat / ser / lab
104591      5249.9900      5249.9900      29.38706      2297.4702      0.0000      485.9514

Thanks in advance for your help.
0
Comment
Question by:princeservice
[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
  • 6
  • 5
12 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41810569
Would this help?
SELECT  Job, SUM(Total_Price), SUM(Invoice_Amount) AS pri, SUM(hrs2) AS hrs, SUM(material3) AS mat, SUM(service2) AS ser, SUM(labor2) AS lab
FROM  dbo.V_Cust_Cost_Det_Inv_Date_B
WHERE  (Invoice_Date BETWEEN '9/1/16' AND '9/21/16')
GROUP BY Job
ORDER BY 2

Open in new window

0
 

Author Comment

by:princeservice
ID: 41810651
Vitor - You are summing the total price. That is what I am trying to avoid.
0
 

Author Comment

by:princeservice
ID: 41810654
I think I have solved it myself. I can just take the average of the total price.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41810662
It returns something like this:

 Job / Total_Price / pri / hrs / mat / ser / lab
 104591      5249.9900      4749.9900      26.589133      2078.7292      0.0000      439.6842
 104591      5249.9900      500.0000      2.797928      218.7410      0.0000      46.2672

In the end this is what I want to see:

 Job / Total_Price / pri / hrs / mat / ser / lab
 104591      5249.9900      5249.9900      29.38706      2297.4702      0.0000      485.9514

Looks to me a SUM of the above lines.
Can you share your solution so I can understand what was really the requirement?
0
 

Author Comment

by:princeservice
ID: 41810664
Vitor, The end result was summing the pri column, not the total_price column.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41810679
Mostly all columns but the total_price column, right?
0
 

Author Comment

by:princeservice
ID: 41810683
Nevermind, I do not have it solved
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41810729
So, what do still missing?
0
 

Author Comment

by:princeservice
ID: 41810957
I probably need to expand on the question. I did not give you enough information. Let's start over:

Let's say I run the following query:

SELECT        TOP (100) PERCENT Job, Total_Price, Invoice_Amount AS pri, hrs2 AS hrs, material3 AS mat, service2 AS ser, labor2 AS lab
FROM            dbo.V_Cust_Cost_Det_Inv_Date_B
WHERE        (Invoice_Date BETWEEN '9/1/16' AND '9/21/16')
ORDER BY Total_Price

It returns something like this:

Job / Total_Price / pri / hrs / mat / ser / lab
104589      5698.2564      5698.2564      124.5633      1255.3665      0.0000      655.5556
104590      12345.0000      12345.0000      3232.23     111.1255      0.0000     96.6600
104591      5249.9900      4749.9900      26.589133      2078.7292      0.0000      439.6842
104591      5249.9900      500.0000      2.797928      218.7410      0.0000      46.2672

What I want is to be able to query the view and get sums of pri / hrs / mat / ser / lab but only the distinct value of Total_Price GROUPED BY JOB. I don't want the Total_Price to sum because that number is the same for every similar job. So far I have this which does not include the Total_Price:

SELECT        SUM(Invoice_Amount) AS pri, SUM(hrs2) AS hrs, SUM(material3) AS mat, SUM(service2) AS ser, SUM(labor2) AS lab
FROM            dbo.V_Cust_Cost_Det_Inv_Date_B
WHERE        (Invoice_Date BETWEEN '9/1/16' AND '9/21/16')

If somehow I could incorporate something like this into the query (Please don't let this throw you off from a good solution. This might be the absolute wrong way to do it):

SELECT SUM(tpri) AS tpri FROM (SELECT DISTINCT Job, Total_Price AS tpri FROM V_Cust_Cost_Det_Inv_Date_B WHERE (Invoice_Date BETWEEN '9/1/16' AND '9/21/16')) AS derivedtbl_1

In the end this is what I want to see:

Job / Total_Price / pri / hrs / mat / ser / lab
104589      5698.2564      5698.2564      124.5633      1255.3665      0.0000      655.5556
104590      12345.0000      12345.0000      3232.23     111.1255      0.0000     96.6600
104591      5249.9900      5249.9900      29.38706      2297.4702      0.0000      485.9514

Notice that job 104589 and 104590 are fine because I never had to sum anything but job 104591 had two lines so I summed up everything from pri over but only took the distinct value of the Total_Price.

Hope this helps.

Thanks in advance for your help.
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 41812086
So lets try a little variation of my first solution:
SELECT  Job, Total_Price, SUM(Invoice_Amount) AS pri, SUM(hrs2) AS hrs, SUM(material3) AS mat, SUM(service2) AS ser, SUM(labor2) AS lab
FROM  dbo.V_Cust_Cost_Det_Inv_Date_B
WHERE (Invoice_Date BETWEEN '9/1/16' AND '9/21/16'
GROUP BY Job, Total_Price
ORDER BY Job

Open in new window

0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41833248
princeservice, any feedback on this question?
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

704 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