SQL query to do data mining on customer records in micorosoft access table for VB 6.0

[awyeah]

Suppose I have a table in microsoft access, I use this table in VB 6.0 for my program.

In the table below, Cons relates to consumption of 24 months of each customer, where consumption is from Cons1 until Cons24, and RU is the reading unit. I have 250,000 records, but just for simplicity I show a few samples.

RU   CustomerNo   Cons1   Cons2   Cons3    Cons4   Cons5  .......  Cons24
001    000005     453.4   765.5   454.7    564.6   867.4  .......  675.1
001    000007     575.5   576.7   745.3    464.1   543.1  .......  565.7
001    000016     253.3   763.5   565.7    352.6   853.3  .......  876.4
006    000108     373.0   457.2   547.4    355.7   685.3  .......  675.2
007    000206     674.5   435.7   546.2    456.3   565.7  .......  765.5
007    000231     434.7   564.7   645.7    423.1   544.8  .......  876.6
009    000467     566.8   534.3   676.8    767.2   453.7  .......  436.7
009    000577     745.5   575.1   565.6    565.4   569.6  .......  563.8
010    000753     465.7   234.6   655.7    545.7   565.7  .......  345.3

I wish to write an SQL query to do the following:

1. Firstly, I wish to average of the 24 consumptions (Cons1 to Cons24) all customers. For each customer this will be done using: (sum (Cons1 to Cons24)) / 24.

2. Secondly, I wish to find the number of customers in every RU.

3. Thirdly, for every RU (reading unit), I wish to find the average customer consumption for all of the customers inside that RU. This will use the results from Step 1.

For example, for two RUs:

      RU    CustomerNo  AvgCustCons
      001    000005      436.5
      001    000007      765.4
      001    000016      704.1
      006    000108      657.7
      007    000206      867.1
      007    000231      176.3

This should be like this:

      RU     RUCons 
      001    436.5 + 765.4 + 704.1 = Show the summed value
      006    657.7
      007    867.1 + 176.3 = Show the summed value

In the end, the final result should be something like this:

RU  NoOfCust  RUCons
001     3     436.5 + 765.4 + 704.1 = Show the summed value
006     1     657.7
007     2     867.1 + 176.3 = Show the summed value
009     2     some value here
010     1     some value here

Then later, I can find the AvgRU consumption using this query:
SELECT RU, (NoOfCust/RUCons) FROM MYTABLE


How can this be done, is it possible to do all these things, using one SQL statement?? All help is appreciated.

[Hack]

Try something like this

SELECT RU, COUNT(CustomerNo) AS CustNum, SUM(AVG(Cons1 + Cons2 + etc etc etc + Cons24)) AS RUConsump 
FROM yourtable
GROUP BY RU

What database are you using?

[awyeah]

Thanks for the help, appreciate it. I use Microsoft access database (mdb). I use VB 6.0, using the ADO/DAO recordsets. I will change what is necessary.

[Hack]

No problem...lets us know if you run into any issues.

[awyeah]

Thanks, however the query you designed, gave an error in Microsoft Access 2007... "aggregrate function" something, because of the "AVG". I tried "AVERAGE" also, but the same thing.

Anyway, my desired results are achievable with this query, I have made now:

SELECT RU, COUNT(CustomerNo) AS NoOfCustomers, SUM((N1+N2+N3+N4+N5+N6+N7+N8+N9+N10+N11+N12+N13+N14+N15+N16+N17+N18+N19+N20+N21+N22+N23+N24)/24) AS RUConsumption, (RUConsumption/NoOfCustomers) as AvgRUConsumption FROM Features GROUP BY RU

Thanks alot!

[Hack]

This doesn't apply here because you have solved your problem, but, for future reference, this

gave an error in Microsoft Access 2007... "aggregrate function" something)

doesn't mean anything.

If you are reporting an error message, we would need to know the exact wording.

Thanks and glad you got it fixed.

[awyeah]

Eventhough the problem is fixed,

The error was:

Cannot have aggregate function in expression (SUM(AVG(N1+N2))).

Currently, I am using the same query as you gave me, with only two Consumption columns I have N1 and N2.

Even with all the 24 consumption columns N1 to N24 it gives me the same error message in Microsoft Access 2007.