Summary fields with Detailed records 2

[M8tt]

I have a Table which I need to report summary information from and to assist with my testing of this I was wondering if it is possible to return summary fields against records in a results set without losing visibility of the detailed records which constitute them?

As an example of what I am trying to achieve; if I have a Table with the following 5 records in it: -

Order No. Part Qty
100 A 1
100 A -1
100 A 1
200 B 20
200 B -15

Is it possible to return all 5 of them with columns for summary values (say for Sum of the Qty against Order & Part and a Count against the whole lot)? i.e. -

Order No. Part Qty Sum of Part Count of Records
100 A 1 1 5
100 A -1 1 5
100 A 1 1 5
200 B 18 3 5
200 B -15 3 5

I can make the summary fields by using GROUP BY but in that case I only get 2 records returned when I want to see all 5.

This is probably quite straight forward but I cannot find anything particularly helpful in BOL.

Many Thanks in advance.

 

[r937]

select t.OrderNo
     , t.Part
     , t.Qty
     , dt.SumOfPart
     , dt.CountOfRecords
  from yourtable as t
inner
  join (
       select OrderNo
            , Part
            , sum(Qty) as SumOfPart
            , count(*) as CountOfRecords
         from yourtable
       group
           by OrderNo
            , Part
       ) as dt
    on t.OrderNo = dt.OrderNo
   and t.Part    = dt.part

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[NoCoolHandle]

What about nesting selects in the select clause?

[blue]Select[/blue] OrderNo, Part, Qty , ([blue]select sum[/blue] (part) [blue]from[/blue] [Order] O1 [blue]where[/blue] O1.part = [Order].part),([blue]Select[/blue] count(*) [blue]from[/blue] [Order])
[blue]From[/blue] [Order] [blue]order by[/blue] 1,2

The first of the nested selects is a co-related subquery. That is because it "joins" the outerquery by specifying it's order id is the same as the order id being used inside the inner query.

The second doesn't need to be related as it only refers to the table as a whole.

HTH


Rob

 

[r937]

rob, good one

i thought the count was the count of order/parts like the sum was the sum of order/parts (likely so that percentage by order/part can be determined easily)

but looking at the data again, it's the overall count



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[M8tt]

Rob/Rudy - I've got both ways to work.

Many Thanks.