asked on

SQL SUM by Group

Experts,

I am having an issue trying to SUM based on a certain group. In this I would like to Sum by "OWNER" where BUS_TRAN is SFEPY. Basically the system split a single amount to 2 diff amounts and the only identifier for those split transactions is "OWNER".

GLE_AMT        BRANCH BUS_TRAN      DEAL      GL ACCT      OWNER
0.08             CAYBR      SFEPY      4+7DAU1Y      Tax      8994OCEF
0.13             CAYBR      SFEPY      4+7DAU1Y      Tax      8994OCEF
59.75            CAYBR      PAPER        4+7DAU1Y      NOSTR      8994OCEF
0.35             CAYBR      SFEPY      4+7DAU1Y      Tax      8994OCEF
0.13             CAYBR      SFEPY      4+7DAU1Y      Tax      8994OCEF
0.03             CAYBR      SFEPY      4+7DAU1Y      Tax      8994OCEF
129.74        CAYBR      PAPER 4+7DAU1Y      NOSTR      --8ZWFE4
1.54             CAYBR      SFEPY      4+7DAU1Y      Tax      --8ZWFE4
0.17             CAYBR      SFEPY      4+7DAU1Y      Tax      --8ZWFE4
0.07             CAYBR      SFEPY      4+7DAU1Y      Tax      --8ZWFE4
45        CAYBR      FEEPY      5+7123545      NOSTR      8994DAFA

Here is result I am expecting
Gross      Net      Tax      BUS_TRAN
191.99      189.49      2.5      SFEPY Group 1
45      45      0      FEEPY Group 2

So the Gross in GROUP 1 is sum of all SFEPY and PAPER while everything else goes to Group 2. Bare in mind that I am only interested in GL Tax and Nostro entries

Let me know if there is any confusion and i will post some more examples. FYI, I am running it on DB2.

pritikaa

THE QUESTION IS NOT CLEAR TO WHAT THE RESULTS YOU HAVE POSTED

p_nuts

what is your current query?

normally it would be

select product, sum(revenue) as sure from table group by product

bose3

ASKER

Pritikaa,
Sorry for the confusion. I have uploaded the Excel worksheet and the data is better formatted. I am trying to Sum all the amounts where trans type is SFEPY which is a partial amount of the Gross but since the other part is marked as PAPER, they only way I think I can group is by OWNER.
The OWNER field identifies the amounts before split. For example I am trying to combine the amounts a person received and how much tax he/she paid thus combining both of the amounts will give us the Gross amount. Once we get the Gross amount of all the people then we SUM all the amounts where trans type is SFEPY. The end result will always be 2 groups. SFEPY and NON-SFEPY.
Let me know if it helps.

Data.xls

Ephraim Wangoya

select SUM(GLE_AMT) over (partition by OWNER) as Gross,
Net, --how do you get the net
Tax, --how do you get Tax
BUS_TRAN
from Table1
where BUS_TRAN = 'SFEEPY'

Cboudroz

You are really not clear but I think it's what you need.

SELECT 
	SUM(GROSS) AS GROSS
	, SUM(TAX) AS TAX
	, SUM(NET) AS NET
	, BUSTRAN
FROM 
	(
	SELECT 
		OWNER	
		, SUM(GLE_AMT) AS GROSS
		, SUM(CASE WHEN GLACCT = 'Tax' THEN GLE_AMT ELSE 0 END) AS Tax
		, SUM(CASE WHEN GLACCT = 'Tax' THEN - GLE_AMT WHEN GLACCT = 'NOSTR' THEN GLE_AMT ELSE 0 END) AS NET
		, (SELECT TOP 1 BUSTRAN FROM tblData AS TBL WHERE TBL.OWNER = OWNER AND BUS_TRAN <> 'PAPER') AS BUSTRAN
	FROM  
		tblData
	GROUP BY 
		OWNER
	) ByOwner
GROUP BY 
	BUSTRAN

Open in new window

bose3

ASKER

P NUTS,
I don't have any query right now. Let me see if I could write a formula in Excel then that might help write SQL
Fez

Ephraim Wangoya

Or Partition by BUS_TRAN
select SUM(GLE_AMT) over (partition by BUS_TRAN) as Gross,
Net, --how do you get the net
Tax, --how do you get Tax
BUS_TRAN
from Table1

bose3

ASKER

Cboudroz,
I tried your scirpt and its giving a token error
"17:40:18.969 DBMS PC_DMPR -- [IBM][CLI Driver][DB2/SUN64] SQL0104N An unexpected token "1" was found following "NET, (SELECT TOP". Expected tokens may include: "<space>". SQLSTATE=42601"
Also I made a small change where I want to run the data in a limited time frame with a selected customer to test the results since GL Table queries take forever
Thanks,
Fez

SELECT 
	SUM(GROSS) AS GROSS, 
    SUM(TAX) AS TAX,
    SUM(NET) AS NET,
    BUSTRAN
FROM 
	(
	SELECT 	GLE_RID_OWNER,
    SUM(GLE_AMT_ENTRY) AS GROSS,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME = 'TWHLD' THEN GLE_AMT_ENTRY ELSE 0 END) AS Tax,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME = 'TWHLD' THEN - GLE_AMT_ENTRY WHEN GLE_CDE_GL_SHTNAME = 'NOSTR' THEN GLE_AMT ELSE 0 END) AS NET,
    (SELECT TOP 1 GLE_CDE_BUS_TRAN FROM ls2user.vls_GL_ENTRY AS TBL WHERE TBL.GLE_RID_OWNER = GLE_RID_OWNER AND GLE_CDE_BUS_TRAN <> 'PAPER') AS BUSTRAN
	FROM  
		ls2user.vls_GL_ENTRY
        where GLE.GLE_CID_CUSTOMER = '937GV2YJ'
        AND GLE.GLE_DTE_POSTING in ('03/31/2010','07/01/2010')
	GROUP BY 
		GLE_RID_OWNER
	) ByOwner
GROUP BY 
	GLE_CDE_BUS_TRAN

Open in new window

yuching

Try this

SELECT SUM(GLE_AMT) AS gross,
SUM(CASE WHEN GL_Name <> 'Tax' THEN GLE_AMT ELSE 0 END) AS Net
SUM(CASE WHEN GL_Name = 'Tax' THEN GLE_AMT ELSE 0 END) AS tax,
BUS_TRAN
FROM ls2user.vls_GL_ENTRY
GROUP BY BUS_TRAN

Cboudroz

I don't know the DB2 syntax,

try this:

SELECT 
	SUM(GROSS) AS GROSS, 
    SUM(TAX) AS TAX,
    SUM(NET) AS NET,
    BUSTRAN
FROM 
	(
	SELECT 	GLE_RID_OWNER,
    SUM(GLE_AMT_ENTRY) AS GROSS,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME = 'TAX' THEN GLE_AMT_ENTRY ELSE 0 END) AS Tax,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME = 'TAX' THEN - GLE_AMT_ENTRY WHEN GLE_CDE_GL_SHTNAME = 'NOSTR' THEN GLE_AMT ELSE 0 END) AS NET,
    CASE WHEN MIN(GLE_CDE_BUS_TRAN) <> 'PAPER' THEN MIN(GLE_CDE_BUS_TRAN) ELSE MAX(GLE_CDE_BUS_TRAN) END AS BUSTRAN
	FROM  
		ls2user.vls_GL_ENTRY
    WHERE 
		GLE.GLE_CID_CUSTOMER = '937GV2YJ'
	    AND GLE.GLE_DTE_POSTING in ('03/31/2010','07/01/2010')
	GROUP BY 
		GLE_RID_OWNER
	) ByOwner
GROUP BY 
	GLE_CDE_BUS_TRAN

Open in new window

bose3

ASKER

Yuching,
Your query is good but it doesn't take into consideration that IF SFEPY & PAPER belongs to same OWNER, only then SUM it. I am thinking that it will a 2 level step. First sum all the SFEPY and PAPER (with same owners), once we get that then sum ALL the owners if they contain SFEPY.
I hope the explanation helps. See the worksheet i posted for details in post #33933210
Thanks,
Fez

bose3

ASKER

Experts,
I have attached a worksheet which shows how I am calculating figures on a sample data
Fez

Data.xls

bose3

ASKER

Cboudroz,
I have made some small changes. I think for DB2 it will be
FETCH FIRST 1 ROWS ONLY. I think CASE WHEN MIN works too. I have attached the code with a Join but I am getting this error
"09:53:56.745 DBMS PC_DMPR -- [IBM][CLI Driver][DB2/SUN64] SQL0206N "GLE_AMT" is not valid in the context where it is used. SQLSTATE=42703"

SELECT 
    SUM(GROSS) AS GROSS, 
    SUM(TAX) AS TAX,
    SUM(NET) AS NET,
    BUSTRAN
FROM 
	(
	SELECT 	GLE_RID_OWNER,
    SUM(GLE_AMT_ENTRY) AS GROSS,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME = 'TAX' THEN GLE_AMT_ENTRY ELSE 0 END) AS Tax,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME = 'TAX' THEN - GLE_AMT_ENTRY WHEN GLE_CDE_GL_SHTNAME = 'NOSTR' THEN GLE_AMT ELSE 0 END) AS NET,
    CASE WHEN MIN(GLE_CDE_BUS_TRAN) <> 'PAPER' THEN MIN(GLE_CDE_BUS_TRAN) ELSE MAX(GLE_CDE_BUS_TRAN) END AS BUSTRAN
	FROM  
		ls2user.vls_GL_ENTRY
    join ls2user.vls_customer CUS on CUS.CUS_CID_CUST_ID = GLE.GLE_CID_CUSTOMER
    WHERE 
		GLE.GLE_CID_CUSTOMER = '937GV2YJ'
	    AND GLE.GLE_DTE_POSTING in ('03/31/2010','07/01/2010')
	GROUP BY 
		GLE_RID_OWNER
	) ByOwner
GROUP BY 
	GLE_CDE_BUS_TRAN

Open in new window

bose3

ASKER

Cboudroz,
Sorry the previous error I get is without the Join, after the Join I get this error
"09:59:16.213 DBMS PC_DMPR -- [IBM][CLI Driver][DB2/SUN64] SQL0206N "GLE.GLE_CID_CUSTOMER" is not valid in the context where it is used. SQLSTATE=42703"

Cboudroz

You changed the query,

Some time you are doing sum on GLE_AMT_ENTRY but at one place you kept GLE_AMT in the NET column.

Your join don't looks good, GLE.GLE_CID_CUSTOMER don't refer to any table. Maybe you want to add ls2user.vls_GL_ENTRY as GLE, but you still don't have the column GLE_CID_CUSTOMER in the vls_GL_ENTRY table.

try this:

SELECT 
    SUM(GROSS) AS GROSS, 
    SUM(TAX) AS TAX,
    SUM(NET) AS NET,
    BUSTRAN
FROM 
	(
	SELECT 	GLE_RID_OWNER,
    SUM(GLE_AMT_ENTRY) AS GROSS,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME = 'TAX' THEN GLE_AMT_ENTRY ELSE 0 END) AS Tax,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME = 'TAX' THEN - GLE_AMT_ENTRY WHEN GLE_CDE_GL_SHTNAME = 'NOSTR' THEN GLE_AMT_ENTRY ELSE 0 END) AS NET,
    CASE WHEN MIN(GLE_CDE_BUS_TRAN) <> 'PAPER' THEN MIN(GLE_CDE_BUS_TRAN) ELSE MAX(GLE_CDE_BUS_TRAN) END AS BUSTRAN
	FROM  
		ls2user.vls_GL_ENTRY 
     WHERE 
		GLE.GLE_CID_CUSTOMER = '937GV2YJ'
	    AND GLE.GLE_DTE_POSTING in ('03/31/2010','07/01/2010')
	GROUP BY 
		GLE_RID_OWNER
	) ByOwner
GROUP BY 
	GLE_CDE_BUS_TRAN

Open in new window

SOLUTION

Cboudroz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bose3

ASKER

Ok I changed the Join a bit and got the following error
"10:26:30.395 DBMS PC_DMPR -- [IBM][CLI Driver][DB2/SUN64] SQL0104N An unexpected token "SUM(GROSS) AS GROSS, SUM(TAX) AS TAX, SU" was found following "SELECT ". Expected tokens may include: "<space>". SQLSTATE=42601"

SELECT 
    SUM(GROSS) AS GROSS, 
    SUM(TAX) AS TAX,
    SUM(NET) AS NET,
    BUSTRAN
FROM ls2user.vls_GL_ENTRY GLE
 join ls2user.vls_customer CUS on CUS.CUS_CID_CUST_ID = GLE.GLE_CID_CUSTOMER
	(
	SELECT 	GLE_RID_OWNER,
    SUM(GLE_AMT_ENTRY) AS GROSS,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME = 'TAX' THEN GLE_AMT_ENTRY ELSE 0 END) AS Tax,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME <> 'TAX' THEN GLE_AMT_ENTRY ELSE 0 END) AS NET,
    CASE WHEN MIN(GLE_CDE_BUS_TRAN) <> 'PAPER' THEN MIN(GLE_CDE_BUS_TRAN) ELSE MAX(GLE_CDE_BUS_TRAN) END AS BUSTRAN
	FROM  
		ls2user.vls_GL_ENTRY GLE
    WHERE GLE.GLE_CID_CUSTOMER = '937GV2YJ'
	AND GLE.GLE_DTE_POSTING in ('03/31/2010','07/01/2010')
	
    GROUP BY 
		GLE_RID_OWNER
	) ByOwner
GROUP BY 
	GLE_CDE_BUS_TRAN

Open in new window

bose3

ASKER

sorry look at this. got rid of the Joins and still got the same error as previous post

SELECT 
    SUM(GROSS) AS GROSS, 
    SUM(TAX) AS TAX,
    SUM(NET) AS NET,
    BUSTRAN
    
    FROM ls2user.vls_GL_ENTRY GLE
 
	(
	SELECT 	GLE_RID_OWNER,
    SUM(GLE_AMT_ENTRY) AS GROSS,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME = 'TAX' THEN GLE_AMT_ENTRY ELSE 0 END) AS Tax,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME <> 'TAX' THEN GLE_AMT_ENTRY ELSE 0 END) AS NET,
    CASE WHEN MIN(GLE_CDE_BUS_TRAN) <> 'PAPER' THEN MIN(GLE_CDE_BUS_TRAN) ELSE MAX(GLE_CDE_BUS_TRAN) END AS BUSTRAN
	FROM  
		ls2user.vls_GL_ENTRY GLE
    WHERE GLE.GLE_CID_CUSTOMER = '937GV2YJ'
	AND GLE.GLE_DTE_POSTING in ('03/31/2010','07/01/2010')
	
    GROUP BY 
		GLE_RID_OWNER
	) ByOwner
GROUP BY 
	GLE_CDE_BUS_TRAN

Open in new window

yuching

try this

SELECT
SUM(GROSS) AS GROSS,
SUM(TAX) AS TAX,
SUM(NET) AS NET,
BUSTRAN
FROM(
      SELECT       GLE_RID_OWNER,
SUM(GLE_AMT_ENTRY) AS GROSS,
SUM(CASE WHEN GLE_CDE_GL_SHTNAME = 'TAX' THEN GLE_AMT_ENTRY ELSE 0 END) AS Tax,
SUM(CASE WHEN GLE_CDE_GL_SHTNAME <> 'TAX' THEN GLE_AMT_ENTRY ELSE 0 END) AS NET,
CASE WHEN MIN(GLE_CDE_BUS_TRAN) <> 'PAPER' THEN MIN(GLE_CDE_BUS_TRAN) ELSE MAX(GLE_CDE_BUS_TRAN) END AS BUSTRAN
      FROM ls2user.vls_GL_ENTRY GLE
WHERE GLE.GLE_CID_CUSTOMER = '937GV2YJ'
      AND GLE.GLE_DTE_POSTING in ('03/31/2010','07/01/2010')
GROUP BY GLE_RID_OWNER
      ) ByOwner
GROUP BY       BUSTRAN

Cboudroz

try my last post without changing it.

bose3

ASKER

Yuching,
Your query works for some reason even though Cboudroz query is the same. I wonder why. I am on the right track with your query but I am getting the Tax amount as 0, it should Sum of all the Tax amounts where Bus Trans is SFEPY.
Fez

yuching

Hi bose3,
1) Cboudroz query didnt work due to group by GLE_CDE_BUS_TRAN which the selection query has rename GLE_CDE_BUS_TRAN to BUSTRAN
2) Your Tax amount is zero probably the GLE_CDE_GL_SHTNAME is not in upper case, it's Tax instead of TAX, try this

SELECT
SUM(GROSS) AS GROSS,
SUM(TAX) AS TAX,
SUM(NET) AS NET,
BUSTRAN
FROM(
SELECT GLE_RID_OWNER,
SUM(GLE_AMT_ENTRY) AS GROSS,
SUM(CASE WHEN GLE_CDE_GL_SHTNAME = 'Tax' THEN GLE_AMT_ENTRY ELSE 0 END) AS Tax,
SUM(CASE WHEN GLE_CDE_GL_SHTNAME <> 'Tax' THEN GLE_AMT_ENTRY ELSE 0 END) AS NET,
CASE WHEN MIN(GLE_CDE_BUS_TRAN) <> 'PAPER' THEN MIN(GLE_CDE_BUS_TRAN) ELSE MAX(GLE_CDE_BUS_TRAN) END AS BUSTRAN
FROM ls2user.vls_GL_ENTRY GLE
WHERE GLE.GLE_CID_CUSTOMER = '937GV2YJ'
AND GLE.GLE_DTE_POSTING in ('03/31/2010','07/01/2010')
GROUP BY GLE_RID_OWNER
) ByOwner
GROUP BY BUSTRAN

bose3

ASKER

Cboudroz,
Your query works now. I was missing a GLE reference but I am getting the Tax as 0.
Fez

bose3

ASKER

Yuching,
Brilliant. That solves it. I am gonna run couple of more tests and provide feedback. And you are right about Cboudroz query and his query works now too.
Awesome work Yuching & Cboudroz
Fez

Cboudroz

Yuching,

thanks to correct my query.

bose3

ASKER

Yuching,
All is good so far but I would like to make a small enhancement. The data is split between PAPER, REPRC & SFEPY (See attached image). Is it possible that once we get split then the query combines PAPER & REPRC (see attached image).

SELECT 
    SUM(GROSS) AS GROSS, 
    SUM(TAX) AS TAX,
    SUM(NET) AS NET,
    BUSTRAN
     FROM(
      SELECT       GLE_RID_OWNER,
    SUM(GLE_AMT_ENTRY) AS GROSS,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME = 'TWHLD' THEN GLE_AMT_ENTRY ELSE 0 END) AS Tax,
    SUM(CASE WHEN GLE_CDE_GL_SHTNAME <> 'TWHLD' THEN GLE_AMT_ENTRY ELSE 0 END) AS NET,
    CASE WHEN MIN(GLE_CDE_BUS_TRAN) <> 'PAPER' THEN MIN(GLE_CDE_BUS_TRAN) ELSE MAX(GLE_CDE_BUS_TRAN) END AS BUSTRAN
      FROM  ls2user.vls_GL_ENTRY GLE
    WHERE GLE.GLE_CID_CUSTOMER = '937GV2YJ'
      AND year(GLE.GLE_DTE_POSTING) = 2010 -- in ('03/31/2010')
     -- AND GLE_CDE_BUS_TRAN in ('INTPY','PAPER','REPRC','SFEPY')
      AND GLE.GLE_CDE_BRANCH = 'CAYBR'
      AND GLE.GLE_CDE_GL_SHTNAME in ('TWHLD','NOSTR')
    GROUP BY GLE_RID_OWNER
      ) ByOwner
GROUP BY       BUSTRAN

Open in new window

pic2.PNG

ASKER CERTIFIED SOLUTION

yuching

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bose3

ASKER

Yuching,
Thanks for great help. The report woks as intended. Is it possible to show the customer column (GLE.GLE_CID_CUSTOMER ) as well. So with the amounts, I see the customer as well. Rigth now I just have the filter for one customer and I want to add multiple customers.
Thanks once again.
Fez