need grouping and summ query

Topic

New Reply

I use the tipical ADO conn n VBA for Excel.
Have a table named mytable with 5 fileds
I want to group by first filed the value in first filed and summ the value in filed 4 and filed 5 how to?
Note:
In first filed possible duplicates value.

strucutre of table:
filed1 …. filed4 filed5
4500 45 15
4500 61 8
4501 88 11
6800 1 8
…
7900 145 55
8500 5 21
reslut of query:
4500 106 23
4501 88 11
….

Reply | Quote

Replies

The SQL string would be

SELECT Filed1, Sum(Filed4), Sum(Filed5) FROM MyTable GROUP BY Filed1

Reply | Quote

The SQL string would be

SELECT Filed1, Sum(Filed4), Sum(Filed5) FROM MyTable GROUP BY Filed1

ok! tks.

But i need, now, to integrate a where clausole in the your sql:

where field3=’opt1′ or filed3=’opt2′

how to?
sorry me.

Reply | Quote

The WHERE part goes between the FROM part and the GROUP BY part:

SELECT Filed1, Sum(Filed4), Sum(Filed5) FROM MyTable WHERE Filed3=’opt1′ OR Filed3=’opt2′ GROUP BY Filed1

Reply | Quote

The WHERE part goes between the FROM part and the GROUP BY part:

SELECT Filed1, Sum(Filed4), Sum(Filed5) FROM MyTable WHERE Filed3=’opt1′ OR Filed3=’opt2′ GROUP BY Filed1

ACCCCCCCCCCCCC………..
Error in preposition FROM

SQL1 = “SELECT UO_RADICAMENTO, Sum(CLIENTI_CORRENTISTI), Sum(CLI_POSSES_BANCOMAT) FROM ‘& NOME_TABELLA &’ WHERE COD_MERCATO=’INDV’ OR COD_MERCATO=’PRIV’ GROUP BY UO_RADICAMENTO”

Note:
NOME_TABELLA is a string variable

and the statement AS Summ1 is needed after sum(filed)???
and when i use:

While Not RS1.EOF
TEST = RS1!UO_RADICAMENTO
Wend

instead to have a distinct value of field1 have all value of not gruoing fields!!!

I need to have the distinct value of field filtered and the relative summ

Reply | Quote

The quotes are not correct. It should be

SQL1 = “SELECT UO_RADICAMENTO, Sum(CLIENTI_CORRENTISTI), Sum(CLI_POSSES_BANCOMAT) FROM ” & NOME_TABELLA & ” WHERE COD_MERCATO=’INDV’ OR COD_MERCATO=’PRIV’ GROUP BY UO_RADICAMENTO”

If you don’t specify an alias AS Summ1, ADO will assign a field name such as Expr1001. If you want to assign the names yourself, you can use AS …

If you want to loop through the records, you must include a MoveNext inside the loop:

Code:

Do While Not RS1.EOF
 TEST = RS1!UO_RADICAMENTO
 RS1.MoveNext
Loop

Reply | Quote

The quotes are not correct. It should be

SQL1 = “SELECT UO_RADICAMENTO, Sum(CLIENTI_CORRENTISTI), Sum(CLI_POSSES_BANCOMAT) FROM ” & NOME_TABELLA & ” WHERE COD_MERCATO=’INDV’ OR COD_MERCATO=’PRIV’ GROUP BY UO_RADICAMENTO”

If you don’t specify an alias AS Summ1, ADO will assign a field name such as Expr1001. If you want to assign the names yourself, you can use AS …

If you want to loop through the records, you must include a MoveNext inside the loop:

Code:

Do While Not RS1.EOF
 TEST = RS1!UO_RADICAMENTO
 RS1.MoveNext
Loop

As usual

Reply | Quote