👉 🔥 CLICK HERE! 🔥 👈

Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning.In this lesson well cover: Using SQL DISTINCT for viewing unique valuesYoull occasionally want to look at only the unique values in a particular column. You can do this using SELECT DISTINCT syntax. To select unique values from the month column in the Apple stock prices dataset, youd use the following query:SELECT DISTINCT month FROM tutorial.aapl_historical_stock_priceIf you include two (or more) columns in a SELECT DISTINCT clause, your results will contain all of the unique pairs of those two columns:SELECT DISTINCT year, month FROM tutorial.aapl_historical_stock_priceNote: You only need to include DISTINCT once in your SELECT clause—you do not need to add it for each column name. DISTINCT can be particularly helpful when exploring a new data set. In many real-world scenarios, you will generally end up writing several preliminary queries in order to figure out the best approach to answering your initial question. Looking at the unique values on each column can help identify how you might want to group or filter the data.Using DISTINCT in aggregationsYou can use DISTINCT when performing an aggregation. Youll probably use it most commonly with the COUNT function.In this case, you should run the query below that counts the unique values in the month column.SELECT COUNT(DISTINCT month) AS unique_months FROM tutorial.aapl_historical_stock_priceThe results show that there are 12 unique values (other examples may be less obvious). Thats a small enough number that you might be able to aggregate by month and interpret the results fairly early. For example, you might follow this up by taking average trade volumes by month to get a sense of when Apple stock really moves:SELECT month, AVG(volume) AS avg_trade_volume FROM tutorial.aapl_historical_stock_price GROUP BY month ORDER BY 2 DESCOkay, back to DISTINCT. Youll notice that DISTINCT goes inside the aggregate function rather than at the beginning of the SELECT clause. Of course, you can SUM or AVG the distinct values in a column, but there are fewer practical applications for them. For MAX and MIN, you probably shouldnt ever use DISTINCT because the results will be the same as without DISTINCT, and the DISTINCT function will make your query substantially slower to return results.DISTINCT performanceIts worth noting that using DISTINCT, particularly in aggregations, can slow your queries down quite a bit. Well cover this in greater depth in a later lesson.Sharpen your SQL skills Write a query that separately counts the number of unique values in the month column and the number of unique values in the 'year' column. Try it out See the answer Lutilisation de la commande SELECT en SQL permet de lire toutes les données dune ou plusieurs colonnes. Cette commande peut potentiellement afficher des lignes en doubles. Pour éviter des redondances dans les résultats il faut simplement ajouter DISTINCT après le mot SELECT.Commande basiqueLutilisation basique de cette commande consiste alors à effectuer la requête suivante:SELECT DISTINCT ma_colonneFROM nom_du_tableauCette requête sélectionne le champ ma_colonne de la table nom_du_tableau en évitant de retourner des doublons.Requête pour OraclePour le Système de Gestion de Bases de Données (SGBD) Oracle, cette requête est remplacée par la commande UNIQUE:SELECT UNIQUE ma_colonneFROM nom_du_tableauExemplePrenons le cas concret dune table client qui contient des noms et prénoms:identifiantprenomnom1PierreDupond2SabrinaBernard3DavidDurand4PierreLeroy5MarieLeroy En utilisant seulement SELECT tous les noms sont retournés, or la table contient plusieurs fois le même prénom (cf. Pierre). Pour sélectionner uniquement les prénoms uniques il faut utiliser la requête suivante:SELECT DISTINCT prenom FROM clientCette requête va retourner les champs suivants:prenomPierreSabrinaDavidMarie Ce résultat affiche volontairement quune seule fois le prénom Pierre grâce à lutilisation de la commande DISTINCT qui naffiche que les résultats distincts.IntérêtLutilisation de la commande DISTINCT est très pratique pour éviter les résultats en doubles. Cependant, pour optimiser les performances il est préférable dutiliser la commande SQL GROUP BY lorsque cest possible.Cours recommandés The SELECT DISTINCT command returns only distinct (different) values in the result set. The following SQL statement selects only the DISTINCT values from the Country column in the Customers table:SELECT DISTINCT Country FROM Customers;Try it Yourself » The SELECT DISTINCT statement is used to return only distinct (different) values.Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values. SELECT DISTINCT Syntax SELECT DISTINCT column1, column2, ... FROM table_name; Demo DatabaseBelow is a selection from the Customers table in the Northwind sample database: CustomerID CustomerName ContactName Address City PostalCode Country 1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany 2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico 3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico 4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK 5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden SELECT Example Without DISTINCTThe following SQL statement selects all (including the duplicates) values from the Country column in the Customers table:SELECT Country FROM Customers;Try it Yourself »Now, let us use the SELECT DISTINCT statement and see the result. SELECT DISTINCT ExamplesThe following SQL statement selects only the DISTINCT values from the Country column in the Customers table:SELECT DISTINCT Country FROM Customers;Try it Yourself » The following SQL statement lists the number of different (distinct) customer countries: SELECT COUNT(DISTINCT Country) FROM Customers;Try it Yourself »Note: The example above will not work in Firefox! Because COUNT(DISTINCT column_name) is not supported in Microsoft Access databases. Firefox is using Microsoft Access in our examples. Here is the workaround for MS Access: SELECT Count(*) AS DistinctCountriesFROM (SELECT DISTINCT Country FROM Customers);Try it Yourself »

Items