MySQL/SQL query question

[rmatthew]

I know - not specifically .net; but I thought maybe one of the db gurus might get this. Basically what I have is this. I want to get a total elaptime for each numericid, cdclass and cdsubclass

 

I have two tables as follows

 

table 1:

numericid

cdclass

cdsubclass

 

table 2:

numericid

elaptime

 

table1 and table2 are related by numericid

 

I hope this explains what it is I am after - been at this for hours:(

If I need to clarify I will try

[Madz]

If i could guess, You are trying to take Sum of Fields

Please try some thing like this

 

Select sum(numericID), Sum(CdClass) , Sum(cdSubClass) from Table1;

 

it will return you only one row. containing Total of each row.

 

Tell me one thing, how rows are managed in tables.

Each NUMERIC ID have more then one class and subclasses

 

then it should be some thing like this

 

Select Sum(cdclass) , Sum(cdsubclass) from Tables1 Where numericid = x;

 

x = some digit or id you have specifiied.

 

If you need more please contact me on my email address coz this forum is only for .NET related questions.

[Nerseus]

Actually, if you want a SUM per Class and Subclass, use this:

SELECT t1.CDClass, t1.CDSubclass, SUM(t2.ElapTime)
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t1.NumericID = t2.NumericID
GROUP BY t1.CDClass, t1.CDSubclass

 

The Group By is the key - it defines a grouping so that you can run aggregate queries on columns, such as the SUM() above PER a column (or multiple columns, as shown).

 

You'll have to use the table.column name syntax in the select since some of the columns repeat between tables (it's genereally a good idea to use table.column names in a SELECT instead of just column name).

 

-Nerseus