My Sql Query Help

Hulicat

I have a query that is joining tables and calculating the SUM. It seems to be working, however; the "SUM" is double what it should be. Any ideas: Select, spendaccount, spend.fromcode, spend.keyword, spend.kwtype, SUM (Rev.trans) Trans, SUM (rev.newaccounts) newaccounts, SUM (rev.gross) Gross, SUM (rev.refunds) Refunds, SUM (rev.net) Net, spend.clicks, spend.spend From spend, Rev wherespend.fromcode= rev.fromcode group by spend.fromcode

Regards, Hulicat

Niladri_Biswas

Hi, Which SUM you are talking about? SUM (Rev.trans)? SUM (rev.newaccounts)? SUM (rev.gross)? SUM (rev.refunds)? SUM (rev.net) Net? :^)

Niladri Biswas

Hulicat

Hello and thanks for the reply; I am not sure on the approach but here is what I am trying to do: Spend table-> spend (SUM'd), clicks, kytype account Rev table-> transactions, newaccounts, gross, refunds, net (SUM'd), From spend, Rev wherespend.fromcode= rev.fromcode ----all in one output--------

Regards, Hulicat

Niladri_Biswas

I am assuming certain things. I have created both the tables and has inserted certain values. Please look into it whether you also have the same values or not!

tblSpend

COLUMN NAME DATATYPE

SPENDACCOUNT int
FROMCODE int
KEYWORD varchar(50)
KWTYPE int
CLICKS int
SPEND int

Values in tblSpend are

101 1 KeyWord1 1 1 10
102 2 KeyWord2 1 10 11
103 3 KeyWord3 2 21 15
104 4 KeyWord4 3 22 16
105 5 KeyWord5 5 11 11
106 6 KeyWord6 2 33 12
107 7 KeyWord7 1 111 11
108 8 KeyWord8 2 22 2
109 9 KeyWord9 1 1 1
110 10 KeyWord10 1 2 3

tblRev

COLUMN NAME DATATYPE

NEWACCOUNTS int
GROSS int
REFUNDS int
NET int
TRANS int
FROMCODE int

Values in tblRev are

1 10 20 10 10 1
2 20 30 10 20 2
3 30 40 15 25 3
4 40 50 45 5 4
5 50 100 50 50 5
6 60 400 200 200 6
7 70 100 100 0 7
8 70 100 50 50 7
6 111 300 120 180 6

The Query is

SELECT

	S.SPENDACCOUNT, S.KEYWORD, 
	S.KWTYPE,S.CLICKS,S.SPEND,

	D.FROMCODE,D.TRANS,D.NEWACCOUNTS,
	D.GROSS,D.REFUNDS,D.NET

FROM (

			SELECT 
					T.FROMCODE,
					SUM (R.TRANS) TRANS,
					SUM (R.NEWACCOUNTS) NEWACCOUNTS,	
					SUM (R.GROSS) GROSS,	
					SUM (R.REFUNDS) REFUNDS,	
					SUM (R.NET) NET

			FROM TBLSPEND T, TBLREV R 
			WHERE 
					T.FROMCODE= R.FROMCODE

			GROUP BY T.FROMCODE 

		) D, TBLSPEND S

WHERE S.FROMCODE = D.FROMCODE

OUTPUT:

101 KeyWord1 1 1 10 1 1 10 20 10 10
102 KeyWord2 1 10 11 2 2 20 30 10 20
103 KeyWord3 2 21 15 3 3 30 40 15 25
104 KeyWord4 3 22 16 4 4 40 50 45 5
105 KeyWord5 5 11 11 5 5 50 100 50 50
106 KeyWord6 2 33 12 6 12 171 700 320 380
107 KeyWord7 1 111 11 7 15 140 200 150 50

Hope this helps :)

Niladri Biswas

modified on Tuesday, June 30, 2009 4:26 AM

smcnulty2000

If you want to solve a problem like this; sometimes you can figure out what is going on by running a similar query to the one you do have but instead of using SUM use COUNT. This is just a debugging method, not the solution. I suspect if you did that with your original query all of the lines that you saw double would come out with a count of 2 or higher. Maybe you should try something more like this for your query:

select
spend.account,
spend.fromcode,
spend.keyword,
spend.kwtype,
(select sum(rev.trans) from rev where rev.fromcode=spend.fromcode) trans,
(select sum(rev.newaccounts) from rev where rev.fromcode=spend.fromcode) newaccounts,
(select sum(rev.gross) from rev where rev.fromcode=spend.fromcode) gross,
(select sum(rev.refunds) from rev where rev.fromcode=spend.fromcode) refunds,
(select sum(rev.net) from rev where rev.fromcode=spend.fromcode) net,
spend.clicks,
spend.spend
from spend
order by spend.fromcode

I don't have mysql running so I can't test it myself.

_____________________________ When life hands you marmots, make marmalade.

Hulicat

Thanks "smcnulty2000" and "Niladri_Biswas" your help and input was greatly appreciated. Regards

Regards, Hulicat

Hulicat

I am still trying to get it to work, this one didnot return any results SELECT S.SPENDACCOUNT, S.KEYWORD, S.KWTYPE,S.CLICKS,S.SPEND, D.FROMCODE,D.TRANS,D.NEWACCOUNTS, D.GROSS,D.REFUNDS,D.NET FROM ( SELECT T.FROMCODE, SUM (R.TRANS) TRANS, SUM (R.NEWACCOUNTS) NEWACCOUNTS, SUM (R.GROSS) GROSS, SUM (R.REFUNDS) REFUNDS, SUM (R.NET) NET FROM TBLSPEND T, TBLREV R WHERE T.FROMCODE= R.FROMCODE GROUP BY T.FROMCODE ) D, TBLSPEND WHERE S.FROMCODE = D.FROMCODE This one "hung" the database: Thanks...for the help I think I am close select spend.account,spend.fromcode,spend.keyword,spend.kwtype, (select sum(rev.trans) from rev where rev.fromcode=spend.fromcode) trans, (select sum(rev.newaccounts) from rev where rev.fromcode=spend.fromcode) newaccounts, (select sum(rev.gross) from rev where rev.fromcode=spend.fromcode) gross,(select sum(rev.refunds) from rev where rev.fromcode=spend.fromcode) refunds,(select sum(rev.net) from rev where rev.fromcode=spend.fromcode) net, spend.clicks, spend.spendfrom spend order by spend.fromcode

Regards, Hulicat

Hulicat · modified on Tuesday, June 30, 2009 4:26 AM

Hello Niladri_Biswas, thanks for your help. Actually I know what the problem is I am just can't figure out the query. Example of data a problem. Fromcode Spend.spend rev.gross ABC 10 70 ABC 20 50 ABC 10 20 Results look like the following: rev.gross sum'd + spend.spend Fromcode spend.spend rev.gross abc 140 (expected 90) abc 160 (expected 100) abc etc I want the total for each column based on the fromcode but not joined sums if that makes sense. Thanks and Regards

Regards, Hulicat