search similar item

[roberttrudel]

hi

i use hsqldb for a small application

in my database, i have a table transactionATB
with theses fields

caisse guichet jourSemaine semaine montant annee
32 12 1 2 192 2006
32 12 1 2 201 2007
14 2 2 22 203 2006

i would like to know if i can get every similar item (same
caisse, guicher, jourSemaine, semaine) and calculate the montant average

with the previous data, i would like to get

caisse guichet jourSemaine semaine montant montant moy
32 12 1 2 192 201 196,5

192 is 2006 montant
201 is 2007 montant
so (192 +201)/ 2

if there was the same data but with 2008 year
so (192 +201 + xxx )/ 3

any idea?

thanks

 

[jrbarnett]

I don't have access to hsqldb, but what you need is a fairly simple GROUP BY query using the average value over the group.

Using Jet SQL (Microsoft Access) the syntax is similar to:

SELECT Table.caisse, Table.guichet, Table.jourSemaine, Table.semaine, Avg(Table.montant) AS [montant moy]
FROM Table
GROUP BY Table.caisse, Table.guichet, Table.jourSemaine, Table.semaine;

Basically you need to find out the equivalent of the avg (sometimes it is called Average) function within your database engine.

John