search

anthonydb / practical-sql

Code and Data for the First Edition of "Practical SQL" by Anthony DeBarros, published by No Starch Press (2018).
Other
662 stars 409 forks source link

Code Listing 5-7 returns duplicate rows #24

Closed felixDK closed 4 years ago

felixDK commented 4 years ago

The query as listed on p. 62 and below:

SELECT geo_name, state_us_abbreviation AS "st", (CAST(p0010006 AS numeric(8,1)) / p0010001) * 100 AS "pct_asian" FROM us_counties_2010 ORDER BY "pct_asian" DESC;

returns duplicate rows. One can suppress these with with a Select Distinct, but it's not clear why one should have to.

Results obtained under Postgres 2.3.3c (66) Mac and pgAdmin 4.17

anthonydb commented 4 years ago

Hi, would you please run the follow statement in pgAdmin and post the results here?

SELECT count(*) FROM us_counties_2010;

Thanks.

felixDK commented 4 years ago

It returned:

6286

felixDK commented 4 years ago

As is now clear from running:

SELECT * FROM us_counties_2010 ORDER BY geo_name, state_us_abbreviation;

I somehow have duplicate rows. I'll just drop the tables and reimport them.

anthonydb commented 4 years ago

That should work. It's possible the data was copied into the table twice since there's no primary key set on it.

Thanks for reading Practical SQL, and please post any other questions you have!