SQL Sum question

SQL Sum question

Post by Rick and Barbara Osgoo » Mon, 15 Sep 1997 04:00:00



I am writing an application using VB5 and Access 97. I have a dbListbox
that presents a list of 'Sections' to a user. In the same table as the
section, is a field that tracks the number of hours spent on that
'Section' (WorkTime). Its type is Number. I am now trying to create a
query that will sum the total hours spent on the selected section. I
have tried many combinations but my brain is now fried.

Any help would be appreciated

Rick Osgood

 
 
 

SQL Sum question

Post by Jim Fergus » Mon, 15 Sep 1997 04:00:00


Rick,

Quote:>I am now trying to create a
>query that will sum the total hours spent on the selected section.

dim db as database
dim rst as recordset
dim lngSection as long
dim strSQL as string

set db = 'some db variable

lngSection = dbcombo 'or wherever
strSQL = "select sum([hours]) as SumOfHours from " & _
         "yourtable where [Section] = " & lngSection

set rst = db.openrecordset(strSQL)

txtTotalHours = rst!SumOfHours

You could also do this with a parameter query instead of building up
the entire SQL statement on the fly at run-time.

-- Jim Ferguson, FMS
   http://www.fmsinc.com

 
 
 

1. SQL sum question

Greetings,

I'm trying to construct a view wich contains two columns based on
different sum functions of the same base table. In the following SQL
script view sum2v produces the required results, but I have a feeling
I'm doing things the hard way.

create table sum2 (
        kode   number,
        jaar   number,
        bedrag number
);
insert into sum2 values(1, 96, 1);
insert into sum2 values(1, 96, 2);
insert into sum2 values(1, 96, 3);
insert into sum2 values(2, 96, 4);
insert into sum2 values(2, 96, 5);
insert into sum2 values(2, 96, 6);
insert into sum2 values(1, 97, 7);
insert into sum2 values(1, 97, 8);
insert into sum2 values(1, 97, 9);
insert into sum2 values(2, 97, 10);
insert into sum2 values(2, 97, 11);
insert into sum2 values(2, 97, 12);
commit;
select * from sum2;
create view sum2v96 as
        select kode, jaar, sum(bedrag) sumbedrag
        from sum2
        where jaar = 96
        group by kode, jaar;
select * from sum2v96;
create view sum2v97 as
        select kode, jaar, sum(bedrag) sumbedrag
        from sum2
        where jaar = 97
        group by kode, jaar;
select * from sum2v97;
create view sum2v as
        select a.kode, a.sumbedrag sumbedrag96, b.sumbedrag sumbedrag97
        from sum2v96 a, sum2v97 b
        where a.kode = b.kode;
select * from sum2v;

Please share your valuable SQL knowledge with me (and all other
newsgroup readers) and come up with a more efficient/ellegant
solution.

Thanks in advance,
Rob Mocking

2. Data Shaping: Anybody using it successfully....

3. Sum(Sum(Sum ...

4. SQL and RPC

5. Which is faster Sum(fld1) + Sum(fld2) +...Sum(fld50) -- OR this

6. Sql Query

7. SQL Plus -- need detail lines and sum of Amount, if the sum is not 0

8. LONG truncation problem (v7.3.3)

9. SQL Question - Summing and Grouping

10. Q: sql question(sum across column w/o group by)

11. SQL question: ordering based on SUM/AVG/COUNT

12. Using DISTINCT AND SUM together SQL question

13. SQL - Sum and Group by Question