SQL with duplicates

[sal21]

I use this query:

SELECT POPOLAZ.REG, POPOLAZ.REGIONE, Sum(POPOLAZ.TOT), Count(POPOLAZ.ISTAT), Count(POPOLAZ.PR)
FROM POPOLAZ
WHERE POPOLAZ.REG='01'
GROUP BY POPOLAZ.REG, POPOLAZ.REGIONE

this code work.

but filed PR have a duplicates value, i need a distinct

[HansV]

How about ISTAT?

[sal21]

ISTAT are only unique value.

[HansV]

Please attach a small sample database (it doesn't have to be complete, but it should contain a few regions and provinces)

[sal21]

Please attach a small sample database (it doesn't have to be complete, but it should contain a few regions and provinces)

modified first post with db

[HansV]

See Query3.

TEST.zip

[sal21]

See Query3.


TEST.zip

great!

bu if to get all REG and REGIONI, instead REG=1

In my case are 20 REGIONI

note:
But i need to use in a SQL string for Vb6 and ADO

[HansV]

Why didn't you ask that in your first post?

[sal21]

Why didn't you ask that in your first post?

sorry!

[HansV]

Dim SQL As String
SQL = "SELECT Query2.REG, Query2.REGIONE, Query2.SummaDiTOT, Query2.ContaDiISTAT, Query1.ContaDiPR FROM (SELECT REG, Count(PR) AS ContaDiPR FROM (SELECT REG, PR FROM POPOLAZ GROUP BY REG, PR) AS Q GROUP BY REG) AS Query1 INNER JOIN (SELECT REG, REGIONE, Sum(TOT) AS SummaDiTOT, Count(ISTAT) AS ContaDiISTAT FROM POPOLAZ GROUP BY REG, REGIONE) AS Query2 ON Query1.REG = Query2.REG"

[sal21]

Dim SQL As String
SQL = "SELECT Query2.REG, Query2.REGIONE, Query2.SummaDiTOT, Query2.ContaDiISTAT, Query1.ContaDiPR FROM (SELECT REG, Count(PR) AS ContaDiPR FROM (SELECT REG, PR FROM POPOLAZ GROUP BY REG, PR) AS Q GROUP BY REG) AS Query1 INNER JOIN (SELECT REG, REGIONE, Sum(TOT) AS SummaDiTOT, Count(ISTAT) AS ContaDiISTAT FROM POPOLAZ GROUP BY REG, REGIONE) AS Query2 ON Query1.REG = Query2.REG"

OPS...

I need also a where ANNO=2023

[HansV]

In your sample database, that returns 0 records because all records have ANNO = 2022. But it would be:

Dim SQL As String
SQL = "SELECT Query2.REG, Query2.REGIONE, Query2.SummaDiTOT, Query2.ContaDiISTAT, Query1.ContaDiPR FROM (SELECT REG, Count(PR) AS ContaDiPR FROM (SELECT REG, PR FROM POPOLAZ WHERE ANNO = 2023 GROUP BY REG, PR) AS Q GROUP BY REG) AS Query1 INNER JOIN (SELECT REG, REGIONE, Sum(TOT) AS SummaDiTOT, Count(ISTAT) AS ContaDiISTAT FROM POPOLAZ WHERE ANNO = 2023 GROUP BY REG, REGIONE) AS Query2 ON Query1.REG = Query2.REG"

[sal21]

In your sample database, that returns 0 records because all records have ANNO = 2022. But it would be:

Dim SQL As String
SQL = "SELECT Query2.REG, Query2.REGIONE, Query2.SummaDiTOT, Query2.ContaDiISTAT, Query1.ContaDiPR FROM (SELECT REG, Count(PR) AS ContaDiPR FROM (SELECT REG, PR FROM POPOLAZ WHERE ANNO = 2023 GROUP BY REG, PR) AS Q GROUP BY REG) AS Query1 INNER JOIN (SELECT REG, REGIONE, Sum(TOT) AS SummaDiTOT, Count(ISTAT) AS ContaDiISTAT FROM POPOLAZ WHERE ANNO = 2023 GROUP BY REG, REGIONE) AS Query2 ON Query1.REG = Query2.REG"

Ifact ANNO not Is in demo database.

Modified in original DB and work perfect!
Tks bro