SQL with duplicates

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

SQL with duplicates

Post by 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
You do not have the required permissions to view the files attached to this post.
Last edited by sal21 on 02 May 2024, 11:54, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: SQL with duplicates

Post by HansV »

How about ISTAT?
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

Re: SQL with duplicates

Post by sal21 »

ISTAT are only unique value.

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: SQL with duplicates

Post by HansV »

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

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

Re: SQL with duplicates

Post by sal21 »

HansV wrote:
02 May 2024, 11:33
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

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: SQL with duplicates

Post by HansV »

See Query3.

TEST.zip
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

Re: SQL with duplicates

Post by sal21 »

HansV wrote:
02 May 2024, 12:38
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

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: SQL with duplicates

Post by HansV »

Why didn't you ask that in your first post? :groan:
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

Re: SQL with duplicates

Post by sal21 »

HansV wrote:
02 May 2024, 14:10
Why didn't you ask that in your first post? :groan:
sorry!

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: SQL with duplicates

Post by 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"

:evilgrin:
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

Re: SQL with duplicates

Post by sal21 »

HansV wrote:
02 May 2024, 14:16
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"

:evilgrin:
OPS...

I need also a where ANNO=2023

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: SQL with duplicates

Post by 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"
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

Re: SQL with duplicates

Post by sal21 »

HansV wrote:
06 May 2024, 09:20
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