Re: Using SUM and GROUP BY


"Ben" <bs2k1@xxxxxxxxxx> wrote...

I have a table that stores hours, minutes, seconds and quantity.
I can find the quantity per hour by selecting hours and quantity
and group by hours with a sum for the quantity.

I'm not clear about what each row would represent?

Does it possibly represent some elapsed time, which gives a specific
quantity?

Then I don't understand what you think the result of such queries would
give.

e.g. if you have records like this:

1 0 10
1 1 10
1 1 20
2 0 10
2 5 20

The result of the grouping you used above would give

1 40
2 30

Is that what you want?

However if I want minutes I need to multiply the hours by
60 and add them to the minutes and then find the sum of the
quantity.

I have tried the following sql statement.

SELECT Sum((hours*60)+minutes) AS mins, Sum(quantity) AS quant FROM
table1GROUP BY mins;

but keep getting this error

ERROR: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]
Too few parameters. Expected 1.

Well, you can't GROUP by the aggregated function, as the GROUP BY is the
criteria on *how* to aggregate...

And I'm not sure why you're using SUM on the hours and minutes anyway if you
want to group by them. If you group by them, there will only be one row to
SUM anyway...

Here you have to choose what you want the result to be.

Either you want something that sums it all:

SELECT Sum((hours*60)+minutes) AS mins, Sum(quantity) AS quant
FROM table1;


Gives:

427 70

Or you want something that distinguish each "timeframe".

SELECT (hours*60+minutes) AS mins, Sum(quantity) AS quant
FROM table1
GROUP BY (hours*60+minutes);

....given the same records as above, you would get:

60 10
61 30
120 10
125 20

Is that what you want?

// Bjorn A



Inviato da X-Privat.Org - Registrazione gratuita http://www.x-privat.org/join.php
.

Relevant Pages

  • Re: Adding Time over 24 hours
    ... To get the sum of the times, create a calculated field in the query to get ... Going back to your original query: ... You'll need to make sure that the Hours & Mins fields are in the query ...
    (comp.databases.ms-access)
  • Using SUM and GROUP BY
    ... I have a table that stores hours, minutes, seconds and quantity. ... find the quantity per hour by selecting hours and quantity and group by ... and add them to the minutes and then find the sum of the quantity. ... SELECT Sum+minutes) AS mins, SumAS quant FROM ...
    (comp.lang.java.databases)
  • Re: Adding Time over 24 hours
    ... If you have Hours and Mins in separate fields, ... HoursWorked come from that you're trying to sum? ... I suspect you've taken the two fields and combined them somehow in ...
    (comp.databases.ms-access)
  • Re: Optimising the Query
    ... SUM ... FROM TABLE1 TAB1 ... FROM TABLE3 ... The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN ...
    (comp.databases.oracle.misc)
  • Re: Recordsetclone mistake
    ... It's easy to get the sum by using a query for these two ... Table1 is actually a variable name (there are some tables ... >> edit data there from this form. ... >picks up the necessary fields from Table2. ...
    (microsoft.public.access.formscoding)