group by and summ

[sal21]

Based the table attached how to group by TEST3 and summ the VE+VI on TOTALE?

In this case:

CATANIA = 56
NAPOLI = 59

[HansV]

Isn't it simply

SELECT TEST3, Sum(TOTALE) AS SUMMATOTALE FROM TEST GROUP BY TEST3

?

[sal21]

Isn't it simply

SELECT TEST3, Sum(TOTALE) AS SUMMATOTALE FROM TEST GROUP BY TEST3

?

work fine but into the filed TEST9 i can have also othe value VR, VS, FV... ecc
I need to filter only Ve and Vi

[HansV]

That wasn't clear from your sample database. Try

SELECT TEST3, Sum(TOTALE) AS SUMMATOTALE FROM TEST WHERE TEST9 IN ('VE','VI') GROUP BY TEST3

[sal21]

That wasn't clear from your sample database. Try

SELECT TEST3, Sum(TOTALE) AS SUMMATOTALE FROM TEST WHERE TEST9 IN ('VE','VI') GROUP BY TEST3

if i understand, for 3 filter item in TEST9 ...

('VE','VI','VR')

correct?

[HansV]

Yes, indeed.

[sal21]

Yes, indeed.

.... based this.
admit have a string=DF-ER-FG or ED-TG... ecc
how to fill dinamiclly the filter ('DF',ER','FG') or ('ED','TG')...

I hope you have understand me

Note:
peraph splitting string from "-"?

[HansV]

Do you build the SQL string in code?

[sal21]

Do you build the SQL string in code?

yes, sure!

[HansV]

You could use code like this:

Code: Select all

Dim strSQL As String
Dim strFilter As String

strFilter = "DF-ER-FG"

strSQL = "SELECT TEST3, Sum(TOTALE) AS SUMMATOTALE FROM TEST WHERE TEST9 IN ('" & _
  Replace(strFilter, "-", "','") & "') GROUP BY TEST3"

[sal21]

You could use code like this:

Code: Select all

Dim strSQL As String
Dim strFilter As String

strFilter = "DF-ER-FG"

strSQL = "SELECT TEST3, Sum(TOTALE) AS SUMMATOTALE FROM TEST WHERE TEST9 IN ('" & _
  Replace(strFilter, "-", "','") & "') GROUP BY TEST3"

sorry for delay i test it now...

[sal21]

You could use code like this:

Code: Select all

Dim strSQL As String
Dim strFilter As String

strFilter = "DF-ER-FG"

strSQL = "SELECT TEST3, Sum(TOTALE) AS SUMMATOTALE FROM TEST WHERE TEST9 IN ('" & _
  Replace(strFilter, "-", "','") & "') GROUP BY TEST3"

Perfect!

... but, in this case, the var strFilter is filled from a result of loop (for next) and i can have also a single vlaue for example FC, how to check when i need to use Replace(strFilter, "-", "','"), peraphs:

Code: Select all

if instr(strFilter is present "-") then 
strSQL = "SELECT TEST3, Sum(TOTALE) AS SUMMATOTALE FROM TEST WHERE TEST9 IN ('" & _
  Replace(strFilter, "-", "','") & "') GROUP BY TEST3"
else 
...wath?
end if

[HansV]

The code should also work correctly if strFilter doesn't contain "-".

[sal21]

You could use code like this:

Code: Select all

Dim strSQL As String
Dim strFilter As String

strFilter = "DF-ER-FG"

strSQL = "SELECT TEST3, Sum(TOTALE) AS SUMMATOTALE FROM TEST WHERE TEST9 IN ('" & _
  Replace(strFilter, "-", "','") & "') GROUP BY TEST3"

I m sorry, but have changed the field TEST9 property in Number and the strFiletr = "265-266-267-268-269-270"..
When the value in TEST9 are letter the sql work but with number, no.
Peraphs i need to use chr(34) in replace statement?

[HansV]

No, you need to omit the single quotes:

Code: Select all

strSQL = "SELECT TEST3, Sum(TOTALE) AS SUMMATOTALE FROM TEST WHERE TEST9 IN (" & _
  Replace(strFilter, "-", ",") & ") GROUP BY TEST3"

[sal21]

No, you need to omit the single quotes:

Code: Select all

strSQL = "SELECT TEST3, Sum(TOTALE) AS SUMMATOTALE FROM TEST WHERE TEST9 IN (" & _
  Replace(strFilter, "-", ",") & ") GROUP BY TEST3"

hI Hans... based the this prob is possible to know how many items are in the string strFilter???

[HansV]

The number of items is

UBound(Split(strFilter, "-")) + 1

or

Len(strFilter) - Len(Replace(strFilter, "-", "")) + 1

[sal21]

The number of items is

UBound(Split(strFilter, "-")) + 1

or

Len(strFilter) - Len(Replace(strFilter, "-", "")) + 1

TKS!