Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help with Select Query using SUM and DISTINCT

Author  Topic 

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-05 : 09:05:34
I have a query that I have to expand one more level to get the results that I need, I started with this query:

SELECT CAST(detail_record.ticket_number AS int) AS TicketNumber, cmDelvTo.customer_number,
cmBillTo.customer_number, CAST(detail_record.pickup_dt AS datetime) AS PickupDate,
CAST(detail_record.deliver_dt AS datetime) AS DeliverDate,detail_record.division AS Division,
detail_record.pickup_weight AS PickupPounds, detail_record.ddp_weight AS LoadPounds,
detail_record.hauler_number AS HaulerNumber
FROM detail_record INNER JOIN
customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number
WHERE (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR
(detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)
ORDER BY detail_record.pickup_dt

and the results were:

I then modified the query to this to get rid of the repeating rows in the Division column:

SELECT CAST(detail_record.ticket_number AS int) AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber,
cmBillTo.customer_number AS BillToNumber, CAST(detail_record.pickup_dt AS datetime) AS PickupDate,
CAST(detail_record.deliver_dt AS datetime) AS DeliverDate,detail_record.division AS Division,
SUM(detail_record.pickup_weight) AS PickupPounds,SUM(DISTINCT detail_record.ddp_weight) AS LoadPounds,
detail_record.hauler_number AS HaulerNumber
FROM detail_record INNER JOIN
customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number
WHERE (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR
(detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)
GROUP BY cmBillTo.customer_number, cmDelvTo.customer_number, detail_record.pickup_dt, detail_record.ticket_number,
detail_record.division, detail_record.deliver_dt, detail_record.hauler_number
ORDER BY detail_record.pickup_dt

And the results were:

I now need to get the DISTINT SUM of the LoadPounds for the PickupDate.

Example;
PickupDate LoadPounds
2009-01-31 17749
2009-01-31 17749
2009-01-31 43076
2009-01-31 43076
DistinctSum 60825

Thanks for the help
CoachBarker

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-05 : 09:18:33
[code]DECLARE @Sample TABLE
(
i INT
)

INSERT @Sample
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 200 UNION ALL
SELECT 200 UNION ALL
SELECT 300

SELECT SUM(DISTINCT i)
FROM @Sample[/code]


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-05 : 13:02:55
you last posted query is not correct. please post corrected query
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-05 : 13:34:41
I know it isn't, that was why I was trying to see how a union would be used.

All we need to do is get the ticket number and the loads pounds and separate them out and still be able to use them in the report.

Thanks for the help
CoachBarker
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-06 : 13:00:44
So I have my original query

SELECT CAST(detail_record.ticket_number AS int) AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber,
cmDelvTo.customer_name AS DeliverToName, cmDelvTo.city AS DeliverToCity,
cmBillTo.customer_number AS BillToNumber, cmBillTo.customer_name AS BillToName, cmBillTo.city AS BillToCIty,
CAST(detail_record.pickup_dt AS datetime) AS PickupDate, CAST(detail_record.deliver_dt AS datetime)
AS DeliverDate, detail_record.division AS Division, SUM(detail_record.pickup_weight) AS PickupPounds,
SUM (DISTINCT detail_record.ddp_weight) AS LoadPounds,detail_record.hauler_number AS HaulerNumber
FROM detail_record INNER JOIN
customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number

WHERE (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR
(detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)

GROUP BY cmBillTo.customer_number, cmBillTo.customer_name, cmBillTo.city, cmDelvTo.customer_number, cmDelvTo.customer_name, cmDelvTo.city,
detail_record.pickup_dt, detail_record.ticket_number,detail_record.division, detail_record.deliver_dt, detail_record.hauler_number,
detail_record.pickup_date, detail_record.ddp_weight
Order BY detail_record.pickup_dt

And I have done this

CREATE TABLE distinct_loadpounds
(
ticket_number int,
ddp_weight int
)
INSERT INTO distinct_loadpounds
SELECT ticket_number,SUM(Distinct ddp_weight)
FROM detail_record
Group By ticket_number

How do I use them together?
Thanks for the help
CoachBarker
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-07 : 13:40:17
join on to main query on ticket_number field
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-08 : 11:28:35
How I haven't been able to find any examples of how to do that.

Thanks for the help
CoachBarker
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-08 : 12:52:24
something like

SELECT CAST(detail_record.ticket_number AS int) AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber,
cmDelvTo.customer_name AS DeliverToName, cmDelvTo.city AS DeliverToCity,
cmBillTo.customer_number AS BillToNumber, cmBillTo.customer_name AS BillToName, cmBillTo.city AS BillToCIty,
CAST(detail_record.pickup_dt AS datetime) AS PickupDate, CAST(detail_record.deliver_dt AS datetime)
AS DeliverDate, detail_record.division AS Division, SUM(detail_record.pickup_weight) AS PickupPounds,
SUM (DISTINCT detail_record.ddp_weight) AS LoadPounds,detail_record.hauler_number AS HaulerNumber,
MAX(ddp_weight) AS ddp_weight
FROM detail_record INNER JOIN
customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number
INNER JOIN distinct_loadpounds dl
ON dl.ticket_number= detail_record.ticket_number

WHERE (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR
(detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)

GROUP BY cmBillTo.customer_number, cmBillTo.customer_name, cmBillTo.city, cmDelvTo.customer_number, cmDelvTo.customer_name, cmDelvTo.city,
detail_record.pickup_dt, detail_record.ticket_number,detail_record.division, detail_record.deliver_dt, detail_record.hauler_number,
detail_record.pickup_date, detail_record.ddp_weight
Order BY detail_record.pickup_dt
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-08 : 13:25:13
I will break out my work laptop and see how that works, I see the logic in it, but sometimes queriies seem logical but still don't compile.

Thanks for the help
CoachBarker
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-08 : 13:27:13
let me know how you got on
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2009-03-08 : 14:18:37
What's the primary key of detail_record?
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-08 : 14:46:34
Actually the PK of the detail_record is detail_number. Why? It isn't in the query because it isn't required in the report. The relationship is one detail_number can have many ticket_numbers.

Thanks for the help
CoachBarker
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-09 : 09:05:15
The query as it is returns the same values as the original LoadPounds column, maybe I did not explain correctly, if so I am sorry. SUM of the MAX LoadPounds For the PickupDate. If I am wording it correctly.

Like this example:
TicketNumberPickupDate LoadPounds
567435 2009-01-31 17749
567435 2009-01-31 17749
567634 2009-01-31 43076
567634 2009-01-31 43076
DistinctSum 60825


Thanks for the help
CoachBarker
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2009-03-09 : 10:10:30
So if you want a result that has one row per ticket_number, why are you including division in the columns that the query is grouped by, when ticket_number does not uniquely determine the value of division?

quote:
Originally posted by CoachBarker

Actually the PK of the detail_record is detail_number. Why?



I was hoping it might shed some light on what it is you're trying to group rows in detail_record on to get your sums of weights.

I suppose I could ask where detail_numbers come from, and whether there are any other unique constraints on detail_record, but I fear the answers will be "it's an IDENTITY" and "no".

You see, there are a number of things I don't get about detail_record on the evidence of the data you've posted. The values of several columns (customer_bill_to, customer_delv_to, pickup_date (or is it pickup_dt?), deliver_dt, ddp_weight, hauler_number) seem to be determined by the ticket_number. Moreover, ddp_weight is the sum of the pickup_weights for that ticket_number. Is that always true? If it is, either detail_record is a really a view that joins two (or more) other tables or detail_record is massively denormalized.
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-09 : 10:49:23
Because in the report Division is the lowest grouping, and unfortunately as far as the design of the tables I have no control over that.

Tables;
1. detail_record
2. customer_master(cmDelv and cmBillTo)

In my report I need to get the PickupPounds(which is the sum of the pickup_weight in the detail_ record based on the number of the same ticket_numbers)

Then I also need the LoadPounds(which is the SUM of the ddp_pounds for the same ticket_number) and there is where the problem is, there can be multiple LoadPounds because there are mutiple ticket_numbers.


Like this example:
TicketNumberPickupDate LoadPounds
567435 2009-01-31 17749
567435 2009-01-31 17749

567634 2009-01-31 43076
567634 2009-01-31 43076


So we need to get the sum of 17749 and 43076 which is 60825.

If your confused guess how I feel


Or would it be the MAX of LoadPounds each ticket_number and/or pickup_dt


Thanks for the help
CoachBarker
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-09 : 12:46:34
Can the last bit of this problem be done in the report using an expression to sum up the load pounds (getting rid of the repeating numbers)???

Now shouldn't the part of the query in red be returning the SUM of the ddp_weight filed minus the repeating numbers? And they are being put in a temp table called dl?


SELECT CAST(detail_record.ticket_number AS int) AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber,
cmDelvTo.customer_name AS DeliverToName, cmDelvTo.city AS DeliverToCity, cmBillTo.customer_number AS BillToNumber,
cmBillTo.customer_name AS BillToName, cmBillTo.city AS BillToCIty, CAST(detail_record.pickup_dt AS datetime) AS PickupDate,
CAST(detail_record.deliver_dt AS datetime) AS DeliverDate, detail_record.division AS Division, SUM(detail_record.pickup_weight) AS PickupPounds,
detail_record.hauler_number AS HaulerNumber, SUM(DISTINCT dl.ddp_weight) AS MaxLoadPounds
FROM detail_record INNER JOIN
customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number INNER JOIN
distinct_loadpounds AS dl ON dl.ticket_number = detail_record.ticket_number
WHERE (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR
(detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)
GROUP BY cmBillTo.customer_number, cmBillTo.customer_name, cmBillTo.city, cmDelvTo.customer_number, cmDelvTo.customer_name, cmDelvTo.city,
detail_record.pickup_dt, detail_record.ticket_number, detail_record.division, detail_record.deliver_dt, detail_record.hauler_number,
detail_record.pickup_date, detail_record.ddp_weight, dl.ddp_weight
ORDER BY detail_record.pickup_dt


Thanks for the help
CoachBarker
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 03:19:22
the last bit can be done in report using IIF() expression. It doesnt subtract repeating numbers, it just sums up distinct values in ddp_weight field, so result wont alwyas be what you're looking at. you may be btter of returning another bit field to distinguish unique values of group and then sum based on it
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-10 : 06:14:52
An example if you would please.

Thanks for the help
CoachBarker
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 06:23:42
suppose if your bit to identify distinct records is dist(you need to return it in your query), then in report just do
SUM(IIF(Fields!dist.value=1,Fields!ddp_weight.value,0))
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-10 : 06:31:35
So are you saying to count the rows of the bit such as COUNT(ddp_weight) then base the IIF off that?

Thanks for the help
CoachBarker
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 06:36:17
sorry didnt understand what you're telling. for count you need this

COUNT(IIF(Fields!dist.value=1,Fields!ddp_weight.value,Nothing))
Go to Top of Page
    Next Page

- Advertisement -