SQL query keep showing duplicates
I have a table with countries, regions, and different measurements. I'm trying to sum a specific column for all equal regions, and then show them for each country. The problem is, countries appear more than once in my table because another column is a "consumption category".
Right now my query is:
SELECT main.country, main.region, (SELECT SUM(sec.share) FROM data_xlsx_Hoja2 sec WHERE sec.region = main.region AND sec.segment="lowest" AND sec.category="food") as total FROM data_xlsx_Hoja2 main
The result is like this:
+ --------+-------------------------+-------------------+ | country | region | total | + --------+-------------------------+-------------------+ | Albania | Europe and Central Asia | 8.152791917324066 | | Albania | Europe and Central Asia | 8.152791917324066 | | Albania | Europe and Central Asia | 8.152791917324066 | | Albania | Europe and Central Asia | 8.152791917324066 | | Albania | Europe and Central Asia | 8.152791917324066 | | Albania | Europe and Central Asia | 8.152791917324066 | | Albania | Europe and Central Asia | 8.152791917324066 | | Albania | Europe and Central Asia | 8.152791917324066 | | Albania | Europe and Central Asia | 8.152791917324066 | + --------+-------------------------+-------------------+
I need my query to show each country just one, but with the number that already shows the query.... I tried to use GROUP BY and SELECT DISTINCT but the query just keep loading and never shows the result. The table has around 30000 rows.
Any ideas? Thanks!!!
Eliminating Duplicate Rows from MySQL Result Sets, When the result set from a SELECT statement contains duplicate rows, you may want to remove them and keep every row data to be unique for a column or combination of columns. You can use the DISTINCT or DISTINCTROW identifier to eliminate duplicate records. The SQL DISTINCT keyword, which we have already discussed is used in conjunction with the SELECT statement to eliminate all the duplicate records and by fetching only the unique records. Syntax The basic syntax of a DISTINCT keyword to eliminate duplicate records is as follows.
My first observation is: Do you really want the share by region or by country? That makes more sense, and would look something like this:
SELECT h.region, h.country, SUM(h.share) FROM data_xlsx_Hoja2 h WHERE h.segment = 'lowest' AND h.category = 'food' GROUP BY h.region, h.country;
If you want the region sum per country, then you need to get a list of countries. Something like this:
SELECT rc.*, r.region_share FROM (SELECT DISTINCT h.region, h.country FROM data_xlsx_Hoja2 h ) rc LEFT JOIN (SELECT h.region, SUM(h.share) as region_share FROM data_xlsx_Hoja2 h WHERE h.segment = 'lowest' AND h.category = 'food' GROUP BY h.region ) r ON rc.region = r.region;
How to Eliminate Duplicate Rows in SQL SELECT Statement for , That can be done with a simple SELECT statement. SELECT count(*) AS count_ , favorite_color_ FROM person_ GROUP BY favorite_color_ Introduction to SQL DISTINCT operator. The primary key ensures that the table has no duplicate rows. However, when you use the SELECT statement to query a portion of the columns in a table, you may get duplicates. To remove duplicates from a result set, you use the DISTINCT operator in the SELECT clause as follows: SELECT DISTINCT column1, column2,
try this, as i understood your question you need this
SELECT country, region, SUM(share) as total FROM data_xlsx_Hoja2 sec WHERE sec.segment="lowest" AND sec.category="food" group by country, region
please clearify your question so we can give proper ans
Remove duplicates (Power Query) - Excel, (Access most likely just displays SELECT; before you delete the existing text.) Type or paste the following SQL into the query: CREATE UNIQUE INDEX The data is contained in an excel file, which is then manipulated by a BI software called Tableau which lets me write custom sql to extract the data from the xls in different ways. sql duplicates share | improve this question | follow | | | |
Show only duplicate values, Here's a handy query for finding duplicates in a table. In T-SQL you may use "insert" to remove duplicates. other data which is duplicated either wise I have o keep the records, which means the first priority is for to get 0. Generally, the query for finding the duplicate values in one column using the GROUP BY clause is as follows: SELECT col , COUNT ( col ) FROM table_name GROUP BY col HAVING COUNT ( col ) > 1 ;
Prevent duplicate values in a table field using an index, Third, the HAVING clause keeps only duplicate groups, which are groups that have more than one occurrence. To return the entire row for each duplicate row, you Solution Simply use the DISTINCT clause and between the SELECT clause and the fields. Example: SELECT distinct id,name,surname FROM mytable More informations available on this : Link
Finding Duplicates with SQL, I'll also show some variations, such as how to find “duplicates in two columns” The query uses a GROUP BY clause to put all the rows with the same For this article, I'll assume you want to keep the 'first' row—the one with Write Query to Verify Duplicates Exist. The first query we’re going to write is a simple query to verify whether duplicates do indeed exist in the table. For our example, my query looks like this: SELECT username, email, COUNT(*) FROM users GROUP BY username, email HAVING COUNT(*) > 1.