Problem in using Sum in SQL

[nader]

I Have these columns: A,B,C,D,E,F,G,H. F including numbers. I tried this SQL, but didn't succcess with me. May you help?

" SELECT B,C,D,E, ( (SUM(F) where A= 'Tom')-(SUM(F) where A= 'George' and A ='Kevin' ) ) ,LAST(G),LAST(H)FROM Table1 GROUP BY B,C,D,E

[Nightwolf629]

One problem I notice is your where statement for the second SUM():

where A= 'George' and A ='Kevin'

this will always be 0, because 'George' does not = 'Kevin'
you are probably looking for:

where A= 'George' OR A ='Kevin'





Also, I am not sure exactly what the data contains and also not sure what you are trying to find with your query, but I doubt you would need:

GROUP BY B,C,D,E

my guess is that GROUP BY B would probably do the trick.




Please post what errors you are getting from your query (if any).

Also, an explaination of what the table contains and what you are trying to get with your query may help as well.

[Nightwolf629]

actually... now that I look at it again,

you would need to run sub-queries for the SUM()'s
you can't just tack on a where statment after them.

SELECT B,C,D,E, 
((SELECT SUM(F) FROM Table1 where A= 'Tom')-(SELECT SUM(F) FROM Table1 where A= 'George' OR A ='Kevin' ) ) as 'sum_statement' ,
LAST(G),LAST(H)
FROM Table1 
GROUP BY B

I think this may work for you

[nader]

The first code I used is this
" SELECT B,C,D,E, SUM(F),LAST(G),LAST(H)FROM Table1 GROUP BY B,C,D,E "
this will sum all the values in the Column F and ...
Example:
B C D E F G H
Box – Usa – Football – Classic - 10 - POP - 2500
Box Usa - Football – Calssic - 5 - Rock - 1000

The Result is:
Box - Usa - Football – Calssic - 15 - Rock – 1000

-Then I add in new column A
A B C D E F G H
Goerge - Box – Usa – Football – Classic - 5 - POP - 2500
Tom - Box Usa - Football – Calssic - 15 - Rock - 1000
Tom - Box Usa - Football – Calssic - 25 - Rock - 300
Kevin - Box Usa - Football – Calssic - 10 - Rock - 300
Goerge - Box – Usa – Football – Classic - 5 - POP - 2500
Kevin - Box Usa - Football – Calssic -15 - Rock - 300

This will take in the first :
Box – Usa – Football – Classic - the problem is here - Rock – 300
How to sum is by
sum where A = Tom .. the tesult is 40
sum where A= George .. the tesult is 10
sum where A= Kevin .. the tesult is 25

(40)- ( 10+25) = 5

the End Result is :
Box – Usa – Football – Classic - 5 - Rock – 300

[Nightwolf629]

Ok, you have me pretty confused now...


Did you try anything I suggested?
Are you still having a problem?

...Im pretty sure you never even asked a question.

[PeejAvery]

You haven't explained what you are attempting to do with the original SQL statement.

[nader]

I used your code. It gives to each different 4 column the same number of sum all the values in column F. it's mean the result according your code will be

B C D E F G H
Box – Usa – Football – Classic - 5 - Rock – 300

Suit – Eng – Basketball– Classic - 5 - Blues - 3200

But it must sum the specific values that belong to each 4 differevt column B,C,D,E

B C D E F G H
Box – Usa – Football – Classic - 5 - Rock – 300

Suit – Eng – Basketball– Classic - 15 - Blues - 3200

[cjard]

Use this, in combination with your brain, to solve your problem:

SELECT
  SUM(CASE WHEN name = 'Tom' THEN age ELSE 0 END) as sum_all_toms_ages,
  SUM(CASE WHEN name = 'Jeff' THEN age ELSE 0 END) as sum_all_jeffs_ages
FROM
 tblTable