How to write this SQL???

[rainzw]

Table table
colA colB colC
------- -------- --------
a11 b11 c11
a11 b11 c22
a22 b11 c33
a33 b22 c44

try to get the following
colA colB colC
------- ----- -------
a11 b11 (c11 +c22)
a22 b11 c33
a33 b22 c44

In another word, I want to get the sum of colC with the distinct colA and colB.

how to write the sql?

 

[visitor]

SELECT a, b, SUM(c) FROM table_name GROUP BY (a || '-' || b)

RPAD/LPAD can be used for a and b in (a || '-' || b)

请我吃饭吧，求你了。

 

[rainzw]

More question:

how to improve the Query Efficiency?

 

[visitor]

最初由 rainzw 发布
More question:

how to improve the Query Efficiency?

This topic is too big.

 

[oread]

Strictly speaking, queries cannot be measured in terms of efficiency, tables can. Normally the query optimizer built within each RDBM server does a pretty good job at optimizing the resources, however a few things can be done to improve that even further. You must take into account the database structure or it's primary use, i.e. how frequent is this table updated? are there any indices on the table? is a clustered index needed? can a simple heap insert suffice your business requirements? A good DBA should be doing precisely this: fine tuning.

Oh, a good way to start looking into ways to optimize your queries is by turning on the query plan.

 

[rainzw]

thansk so much!

You guys gave me many good suggestions. As a new comer for DB area, I have a lot things need to be learned. And I will be apprieciated that you could recommand me some web sites or book for that because I am still in a mass for some terms.

 

[忙里偷闲]

SELECT a, b, SUM(c) FROM table_name GROUP BY a, b;

足已。

 

[oread]

Type in keyword SQL in google, you should find many useful links. I've also have some slides from Oracle (or maybe Sybase) regarding performance tuning, email me if you need one.