| View previous topic :: View next topic |
| Author |
Message |
Sarangadhar
Beginner
Joined: 14 Oct 2004
Posts: 130
Topics: 43
Location: virtual village
|
 Posted: Tue Dec 04, 2007 5:51 pm Post subject: aggregate same column for different situations |
 |
|
| Code: | Tab1
Col1 Col2
1 A
1 A
2 B
2 B
3 C
3 C |
SEE THE ABOVE TABLE. I want to find the SUM of col1, individually when col2=A, b and C, like below
| Code: | sum(col1) col2
2 A
4 B
6 C |
Is it possible without joining the table 3 times?
_________________
Thanks |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Dec 04, 2007 5:56 pm Post subject: |
|
|
Sarangadhar,
A simple Group BY on COL2 with SUM on col1 should give you the results. Untested sql
| Code: |
SELECT SUM(COL1)
,COL2
FROM TABLE
GROUP BY COL2
;
|
Hope this helps...
Cheers
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Sarangadhar
Beginner
Joined: 14 Oct 2004
Posts: 130
Topics: 43
Location: virtual village
|
| Posted: Tue Dec 04, 2007 6:14 pm Post subject: |
|
|
thanks.. and I apoligize for asking this question ..
how stupid i was.. After being sick for 2 weeks, I am not thinking well.. I totally lost myself.. After writing queries of 300 lines, I have asked this question.. I need to revive my brain. I still need to recover completely i guess
Thanks Kolusu.
_________________
Thanks |
|
| Back to top |
|
|
Sarangadhar
Beginner
Joined: 14 Oct 2004
Posts: 130
Topics: 43
Location: virtual village
|
| Posted: Tue Dec 04, 2007 6:47 pm Post subject: |
|
|
Kolusu,
sorry for asking another question.. I need this again.
2nd column in the above example will always have 3 different values. I wanted 3 SUM values in a single row like this.
can I get this without joining the table 3 times?
| Code: | whenA whenB whenC
2 3 6 |
_________________
Thanks |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Dec 04, 2007 8:58 pm Post subject: |
|
|
Sarangadhar,
You can do with a case statement , here is another untested sql
| Code: |
SELECT SUM(CASE WHEN COL2 = 'A' THEN COL1 ELSE INT(0) END)
,SUM(CASE WHEN COL2 = 'B' THEN COL1 ELSE INT(0) END)
,SUM(CASE WHEN COL2 = 'C' THEN COL1 ELSE INT(0) END)
FROM TABLE
|
Hope this helps...
Cheers
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
