Grouping Data and using the SUM Function

[tomas79]

Hi all, Been working on this issue now for some time as part of my learning curve but am not generating the result I'm after. I would like to know if the approach I'm using is completely wrong or does it just need tweaking. I would appreciate any of your comments and feedback.

The query below generated the output you can see on the at the bottom of this thread. The column are not alligned correctly when pasting into this thread so please copy and paste the output to notepad.

I've also included the result in which I'm after at the bottom of this thread. Can anyone confirm if this is possible using the existing strategy.

QUESTION 1
**********
Is it common for a SQL programmer to then take the result of this query and write a new one or would the programmer generate write the query within another query. Is this a Sub Query.

REQUIREMENT

How can I use the output attached to generate the following information:

A. Group By DOCUMENTNUM_TRIM

B. SUM LT_AMOUNTMST where there is data in DocumentNum_TRIM otherwise do not sum. In this case the data in DOCUMENTNUM_TRIM is 0090969

C. IT_COSTAMOUNTPOSTED where there is data in DocumentNum_TRIM otherwise do not sum. In this case the data in DOCUMENTNUM_TRIM is 0090969

D. take the value from IT_COSTAMOUNTPHYSICAL where VCTCONTRACTTYPEID = Physical

E. There are some records with no DOCUMENTNUM_TRIM but I want them to exist as seperate records within my output. How can this be achieved?

I've also attached the result in which I'm after. Can anyone confirm if this is possible using the existing strategy.


SELECT
LEFT(LT.DOCUMENTNUM,12) AS DOCUMENTNUM_TRIM,
--sum(LT.AMOUNTMST) AS LT_AMOUNTMST
LT.AMOUNTMST AS LT_AMOUNTMST,
(IT.COSTAMOUNTPHYSICAL) AS IT_COSTAMOUNTPHYSICAL,
IT.COSTAMOUNTPOSTED AS IT_COSTAMOUNTPOSTED,
PT.VCTCONTRACTTYPEID
FROM INVENTTRANSPOSTING ITP
INNER JOIN LEDGERTRANS LT ON
ITP.VOUCHER = LT.VOUCHER AND
ITP.TRANSDATE = LT.TRANSDATE
INNER JOIN INVENTTRANS IT ON
IT.INVENTTRANSID = ITP.INVENTTRANSID
LEFT JOIN PURCHTABLE PT ON
PT.PURCHID = IT.TRANSREFID
WHERE
ITP.DATAAREAID = 'CCC'AND
LT.DATAAREAID = 'CCC'AND
LT.ACCOUNTNUM = '9110' AND
ITP.ISPOSTED = '1'

PASTE THIS INTO NOTEPAD (MY EXISTING OUTPUT)
********************************************

DOCUMENTNUM_TRIM LT_AMOUNTMST IT_COSTAMOUNTPHYSICAL IT_COSTAMOUNTPOSTED VCTCONTRACTTYPEID
0090969 2500.00 2500.00 0.00
0090969 1200.00 1200.00 0.00
0090969 999.00 999.00 0.00
0090969 24000.00 28699.00 0.00 Physical

70000.00 98190.00 70000.00 NULL
5000.00 9819.00 5000.00 NULL

PASTE THIS INTO NOTEPAD (WHAT OUTPUT IM AFTER)
**********************************************

OCUMENTNUM_TRIM LT_AMOUNTMST IT_COSTAMOUNTPHYSICAL IT_COSTAMOUNTPOSTED
0090969 28699.00 28699.00 0.00
70000.00 98190.00 70000.00
5000.00 9819.00 5000.00

As you can see I would the records brought through even when DOCUMENTNUM_TRIM is blank.

Thanks and kind regards,
Tomas

 

[gmmastros]

[tt]QUESTION 1
**********
Is it common for a SQL programmer to then take the result of this query and write a new one or would the programmer generate write the query within another query. Is this a Sub Query.[/tt]

Yes. It is common to use the results of one query as the basis for another. There are many ways to accomplish this, each with their own pro's and con's.

You could create a temp table, and then use the first query to insert in to the temp table. Then you could write a query that uses the temp table.

You could create a table variable, and then use the first query to insert in to the table variable. Then you could write a query that uses the table variable.

You could create a query using "Common Table Expressions" (CTE's). The basic syntax would be like this...

;With AnyNameYouWant As (
   Select Col1, Col2
   From   TableName
   Where  SomeColumn = '2'
)
Select * From AnyNameYouWant

You could write a derived table, like this...

Select DerivedTableName.Col1,
       DerivedTableName.Col2
From   (
       Select Col1, Col2
       From   TableName
       Where  SomeColumn = '2'
       ) As DerivedTableName

And... yes... you can write a subquery.

Generally speaking, it is best to avoid writing a query that is used as the basis for another query. Sometimes the query optimizer part of SQL server can generate the same execution plan, but most of the time, performance suffers a little bit. What I mean is... if you need to write a query like this, then do it, but if there is no need, then don't.


[tt]SUM LT_AMOUNTMST where there is data in DocumentNum_TRIM otherwise do not sum. In this case the data in DOCUMENTNUM_TRIM is 0090969

IT_COSTAMOUNTPOSTED where there is data in DocumentNum_TRIM otherwise do not sum. In this case the data in DOCUMENTNUM_TRIM is 0090969
[/tt]

The simplest way to accommodate this would be to use a union all query. Selectively summing and not summing in the same query would be prohibitively difficult. But, writing a union all query would be relatively simple. For example....

Select   Col1, Sum(AmountCol) As Amount
From     TableName
Where    ColX = 'Blah'
Group By Col1 

Union All

Select   Col1, AmountCol
From     TableName
Where    ColX <> 'Blah'

When you write a union all (or union) query, you need to make sure that each query returns the same number of columns, and that each column has the same data type.

[tt]D. take the value from IT_COSTAMOUNTPHYSICAL where VCTCONTRACTTYPEID = Physical[/tt]

This can be accomplished by using a case statement. FOr example:

Select Case When VCTCONTRACTTYPEID = 'Physical'
            Then IT_COSTAMOUNTPHYSICAL
            Else 0
            End As AnyColumnNameAlias
From   TableName

There are a couple rules you should be aware of when using case/when. Each branch of execution should return the same data type. For example, do not mix strings and numbers. In the example shown above, I use the value 0 for else. I could also have used NULL. Choosing 0 or NULL depends on the big picture. If you are summing, it probably does not matter unless ALL of the rows fall in to the 'else' part, in which case 0 or NULL would be returned.

[tt]E. There are some records with no DOCUMENTNUM_TRIM but I want them to exist as seperate records within my output. How can this be achieved?[/tt]

Like I mentioned earlier, this would be a good place to use UNION ALL. You could write the first query where DOCUMENTNUM_TRIM > '', and the second query where DOCUMENTNUM_TRIM is null or ''. This will allow you to combine the data in to a single query.

I encourage you to take my advice and apply it to your problem. If you get stuck, or want further clarification, just ask.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tomas79]

Thanks gmmastros for the excellent feedback and prompt reply.

That gives me plenty og information to work on.

I think I might not of been clear about 2 of the points made.

Let me give you an example
**************************

If the output to my query is:

CostGroup Cost_1 Cost_2 ContractType
1000 2500.00 2500.00
1000 1200.00 1200.00
1000 999.00 999.00
1000 24000.00 28699.00 Physical

I would like to TAKE THE OUTPUT FROM THE QRY ABOVE and write another query to generate the following output (temp table sounds like a good idea - thanks!)

EXPECTED OUTPUT (This is what I'm trying to achieve):
*****************************************************

CostGroup Cost_1 Cost_2
1000 28699.00 28699.00

What I've not been clear about is the following:
************************************************
************************************************

I want to GROUP BY CostGroup but SUM Cost_1 to make 28699.00 and THEN more importantly some how take the value 28699.00 from Cost_2 where ContractType equals Physical.

I'm not interested in summing cost_2. I only need the value where where ContractType equals Physical. in this case its 28699.00.

Hope this clarifies my point and once again thank you.

Regards,
Tomas.

 

[gmmastros]

(temp table sounds like a good idea - thanks!)

In my experience, temp tables are usually the worst performers. There are exceptions to this, but temp tables are usually the last thing I try.

Based on your current output, I will show you 3 techniques to obtain your desired output. In each case, I will create a table variable and hard code your sample data. This allows you to copy/paste the code and run it in a query window to see how it works.

1. Case/when method.

Declare @Temp Table(CostGroup Int, Cost_1 Decimal(10,2), Cost_2 Decimal(10,2), ContractType VarChar(20))

Insert Into @Temp Values(1000,2500.00 ,2500.00 ,'')
Insert Into @Temp Values(1000,1200.00 ,1200.00 ,'')
Insert Into @Temp Values(1000,999.00  ,999.00  ,'')
Insert Into @Temp Values(1000,24000.00,28699.00,'Physical')

Select CostGroup, 
       Sum(Cost_1) As Cost_1,
       Sum(Case When ContractType = 'Physical' Then Cost_2 Else 0 End) As Cost_2
From   @Temp
Group By CostGroup

With this method, you will see that we are summing the cost_2 column, but we are using the value 0 for all rows where ContractType <> 'Physical' and only use the cost_2 when it is Physical.

1. CTE method.

Declare @Temp Table(CostGroup Int, Cost_1 Decimal(10,2), Cost_2 Decimal(10,2), ContractType VarChar(20))

Insert Into @Temp Values(1000,2500.00 ,2500.00 ,'')
Insert Into @Temp Values(1000,1200.00 ,1200.00 ,'')
Insert Into @Temp Values(1000,999.00  ,999.00  ,'')
Insert Into @Temp Values(1000,24000.00,28699.00,'Physical')

;With Cost1 
As (
  Select CostGroup, Sum(Cost_1) As Cost_1
  From   @Temp
  Group By CostGroup
)
Select Cost1.CostGroup,
       Cost1.Cost_1,
       T.Cost_2
From   Cost1
       Inner Join @Temp T
         On  Cost1.CostGroup = T.CostGroup
         And T.ContractType = 'Physical'

Notice that this method joins back the to table but only where contracttype = 'physical'. The first query (in the CTE part) does your SUM.

3. Derived table method.

Declare @Temp Table(CostGroup Int, Cost_1 Decimal(10,2), Cost_2 Decimal(10,2), ContractType VarChar(20))

Insert Into @Temp Values(1000,2500.00 ,2500.00 ,'')
Insert Into @Temp Values(1000,1200.00 ,1200.00 ,'')
Insert Into @Temp Values(1000,999.00  ,999.00  ,'')
Insert Into @Temp Values(1000,24000.00,28699.00,'Physical')

Select  CostSum.CostGroup, 
        CostSum.Cost_1,
        T.Cost_2
From    (
        Select CostGroup, Sum(Cost_1) As Cost_1
        From   @Temp
        Group By CostGroup
        ) As CostSum
        Inner Join @Temp T
          On CostSum.CostGroup = T.CostGroup
Where   T.ContractType = 'Physical'

The derived table method is very similar to the CTE method. The most notable difference is the syntax. Also note that you cannot use CTE's with a SQL2000 database. CTE were introduced starting with SQL2005.

Also of interest, the best performer here is going to be the first on (the case/when) method because the other 2 methods join back to the original table. There is a performance cost associated with each join.

I encourage you to copy/paste each method to a query window and run it. For each query, poke it, prod it, change it, learn it. Each technique is useful and powerful.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom