Robert Francis
asked on
Problem getting distinct value along with sums SQL Server
Let's say I run the following query:
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:
If somehow I could incorporate something like this into the query:
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.
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
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')
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
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.
ASKER
Vitor - You are summing the total price. That is what I am trying to avoid.
ASKER
I think I have solved it myself. I can just take the average of the total price.
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?
ASKER
Vitor, The end result was summing the pri column, not the total_price column.
Mostly all columns but the total_price column, right?
ASKER
Nevermind, I do not have it solved
So, what do still missing?
ASKER
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_Da te_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_Da te_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.
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_Da
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_Da
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
princeservice, any feedback on this question?
Open in new window