Mysql error code 1248 every derived table must have its own alias - Исправление ошибок и поиск оптимальных решений проблем

Are you writing a query in MySQL and getting an error of “1248: Every derived table must have its own alias”? If so, it’s a simple error to fix.

So you’ve got this error in MySQL. How do you fix it?

The short answer is you need to give your subqueries an alias in your SELECT statement. Add an alias after the closing bracket of the FROM clause subquery.

In other SQL vendors, this is not required, but MySQL requires you to alias your subqueries.

What does this mean?

Let’s take a look at an example.

Example of a Derived Table

A derived table is a SELECT subquery within the FROM clause of your main query. It’s treated like a table because it has columns and returns rows. It can be used in place of a table in the FROM clause, for example. It’s often called an inline view or just a subquery.

Here’s an example of a derived table:

SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
  SELECT o.order_id,
  o.customer_city,
  o.order_amount
  FROM orders o
  INNER JOIN customer c ON o.customer_id = c.customer_id
)
GROUP BY customer_city;

This query finds the city, number of orders, and the sum of the order amounts from the orders and customer tables.

Let’s break this query down.

The query has an outer query and an inner query. The inner query is used to get data from orders and customers:

SELECT o.order_id, o.customer_city, o.order_amount
FROM orders o
INNER JOIN customer c ON o.customer_id = c.customer_id

This gets data from two tables, joining on a common field. This is the “derived table”.

The outer query selects a few columns from this subquery. Rather than using a table, the data comes from the results of this subquery.

SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
...
)
GROUP BY customer_city;

It shows the customer_city, two aggregate functions, and groups by the city.

This is the entire query again:

SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
  SELECT o.order_id,
  o.customer_city,
  o.order_amount
  FROM orders o
  INNER JOIN customer c ON o.customer_id = c.customer_id
)
GROUP BY customer_city;

What happens if you run this query in MySQL?

You’ll get this error:

Error 1248: Every derived table must have its own alias

How do you resolve this?

Solution to “Every derived table must have its own alias”

The reason you get this error is that in MySQL, every derived table (subquery that is a SELECT query) needs to have an alias after it.

The query example here did not have an alias for the subquery. The alias for a subquery is the same as a column alias. It goes after the closing brackets for the FROM clause for the subquery.

So, in this example, all we need to do to resolve this error is to add an alias after the closing bracket.

The alias can be almost anything you like: a single letter, a word, a few letters. I often use the word “sub” if it’s a small query (for subquery), or something more descriptive if it’s a longer query.

SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
  SELECT o.order_id,
  o.customer_city,
  o.order_amount
  FROM orders o
  INNER JOIN customer c ON o.customer_id = c.customer_id
) sub
GROUP BY customer_city;

Notice how the word “sub” is added after the closing bracket on the second last line? That’s the alias for the subquery or derived table. This alias is required in MySQL but not other vendors.

Running this query should work and you should not get the “Every derived table must have its own alias” error anymore.

Optional: Add the AS Keyword

You could add the AS keyword, as this is an optional part of adding an alias, but it’s up to you. This will also work:

SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
  SELECT o.order_id,
  o.customer_city,
  o.order_amount
  FROM orders o
  INNER JOIN customer c ON o.customer_id = c.customer_id
) AS sub
GROUP BY customer_city;

So, that’s how you can resolve this derived table alias error. Add an alias to your subquery.

Источник

In this article, we will be discussing the below points

HOW TO FIX THE ERROR in MySQL : Every derived table must have its own alias
Every derived table must have its own alias : JOIN

Let’s get started, but first, we will look into the data we will be using in our examples. Assume that we have a table sale_details with the below rows.

figure 1.1

HOW TO FIX THE ERROR in MySQL : Every derived table must have its own alias

What is a derived table? Well, the Derived table is an expression that creates a temporary table in the FROM clause’s scope in the query. It is a subquery in the SELECT statement and FROM clause. Let’s look into an example to get the average of the maximum no_products_sold for each salesperson for a department. Observe the below query.

 SELECT 
    CEIL(AVG(max_sales))
FROM
    (SELECT 
        sale_person_name, MAX(no_products_sold) AS max_sales
    FROM
        sale_details
    GROUP BY sale_person_name);

In the above query, we created a derived table from subquery “SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name,” which is within the scope of the FROM clause.

On running the query written above to get the average of the maximum no_products_sold for each salesperson per department, we get an ERROR :

Advertisements

Action Output Message: SELECT CEIL(AVG(max_sales)) FROM (SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name) LIMIT 0, 1000 Error Code: 1248. Every derived table must have its own alias 0.00025 sec

It is highlighting that an Error:1248 Every derived table must have its alias has occurred. The reason for the same is that we need to provide an alias for the sub-queries while working in MySQL (alias is just a temporary/false name). Observe the corrected query below:

 SELECT 
    CEIL(AVG(max_sales)) AS average_of_max_sales_per_salesman
FROM
    (SELECT 
        sale_person_name, MAX(no_products_sold) AS max_sales
    FROM
        sale_details
    GROUP BY sale_person_name) AS sales_alias;

In the above query, we have added an alias sales_alias for the derived table. On running the query now, we get the desired result.

Action Output Message: SELECT CEIL(AVG(max_sales)) FROM (SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name)as sales_alias LIMIT 0, 1000 1 row(s) returned 0.00086 sec / 0.000018 sec.

Output :-

figure 1.2

Let’s see how to work with joins while using the derived tables. We will be using the same table sale_details to get all the columns for a salesperson corresponding to the row, which shows the maximum sale for a product department wise. We need to do an inner join of the sales_details table with a derived table for the desired result. Observe the below query, it’s output, and explanation.

SELECT 
    sd1.*
FROM
    sale_details sd1
        INNER JOIN
    (SELECT 
        sale_person_name, MAX(no_products_sold) AS MaxSale
    FROM
        sale_details
    GROUP BY sale_person_name) sd2 ON sd1.sale_person_name = sd2.sale_person_name
        AND sd1.no_products_sold = sd2.MaxSale;

Action Output Message: SELECT sd1.* FROM sale_details sd1 INNER JOIN (SELECT sale_person_name, MAX(no_products_sold) AS MaxSale FROM sale_details GROUP BY sale_person_name) sd2 ON sd1.sale_person_name = sd2.sale_person_name AND sd1.no_products_sold = sd2.MaxSale LIMIT 0, 1000 4 row(s) returned 0.00097 sec / 0.000032 sec.

Output:-

figure 1.3

Explanation:- In this query, we are using an INNER JOIN with the sales_details table and the derived table.

STEP1: The derived table created in the sub-query “SELECT sale_person_name, MAX(no_products_sold) AS MaxSale FROM sale_details GROUP BY sale_person_name” gets the sales_person_name and maximum no_products_sold grouped by sales_person_name. The alias name given to this derived table is sd2.

STEP2: Select all the details from the sales_details table in the outer-query alias name is sd1.

STEP3: Finally, doing an INNER JOIN on the derived table and sales_details table ON sale_person_name AND ON no_products_sold from sales_details table, MAX(no_products_sold) from the derived table.

We hope this article provided a good understanding of the alias while using the derived tables. Good Luck !!!

Advertisements

Thanks for reading.

Источник

If you see MySQL SQL error 1248, this tutorial will help you.

Updated

1. Download ASR Pro

2. Run the program

3. Click «Scan Now» to find and remove any viruses on your computer

Speed up your computer today with this simple download.

You must provide an alias for all your inline mood / view tables in MySQL, even if they are nested. In your code, you are using a view in the following view, but you seem to have forgotten to mention the alias for your company’s internal view.

MYSQL ERROR 1248 (42000): Each view must have its own nickname.

I could not understand why this was a mistake

  SELECT * FROMCHOOSEc.city_id,p.prop_ynow_id,Object id p.,p.prop_add_value,name,Drawing,ifnull (p.address, '') as 'place',ifnull (city, '') as 'ville',ifnull (shortcut, '') as "State",ifnull (p.zip, '') as `zip`,min (if (pr.minrent implies 0.9999999, pr.minrent)) as minrent,max (pr.maxrent) as maxrent,'' during the service,Hood_id,ifnull (p.phone, '') as 'phone',Huge,Longitude,min (CAST (pu.fullBath AS UNSIGNED)) as `minbath`,max (CAST (pu.fullBath AS UNSIGNED)) as `maxbath`,min (CAST (pu.Bed AS UNSIGNED)) as `minbed`,max (CAST (pu.Bed AS UNSIGNED)) as `maxbed`,'' as a URL,'as source_id,'Source name,'' as well as the address code,'' as area code,almost like ctime,'' as paid,like Similar_url,like created_at,mainly because update_at,as the name of the city,'' as service_listing_idFROMwiki_city_list they will join Propertyinfo p at c.city_id = p.cityjoin ynow_rentwiki.Property_Unitlayout Please take away p.prop_ynow_id = pu.P_Identjoin (SELECT CAST (substring_index (if (Rent> 0, Rent, RentLow), '.', 1) AS UNSIGNED) as minrent, CAST (substring_index (if (Loyer> 0, Rent, Renthigh), '.', 1 )) AS UNSIGNED) as maxrent, PRE_Ident, P_Ident, UNL_Ident, RTY_Ident from ynow_rentwiki.Property_rents, where P_Ident in 4576, (3958, 4577) and (Rent! = '' And also (low! = '' And Renthigh! = ' ')))) as page rank on pu.UNL_Ident = pr.UNL_IdentJoin state s on (p.state matches s.stateid OR p.state = s.ShortCut)ORpu.Status = 'Active'and p.delete_date = '0000-00-00'BY GROUPc.city_id,p.prop_ynow_idUNIONCHOOSEc.city_id,p.prop_ynow_id,Object id p.,p.prop_add_value,name,Drawing,ifnull (p.address, '') as 'place',ifnull (city, '') as 'ville',ifnull (shortcut, '') as "State",ifnull (p.zip, '') as `zip`,min (if (pr.minrent matches 0.99999999, pr.minrent)) as minrent,max (pr.maxrent) as maxrent,'for the reason that the serviceHood_id,ifnull (p.phone, '') as 'phone',Huge,Longitude,min (CAST (pu.fullBath AS UNSIGNED)) as `minbath`,max (CAST (pu.fullBath AS UNSIGNED)) as `maxbath`,min (CAST (pu.Bed AS UNSIGNED)) as `minbed`,max (CAST (pu.Bed AS UNSIGNED)) as `maxbed`,'' as a URL,'as source_id,'Source name,'' although as an address code'' as area code,like ctime,'' paid,like Similar_url,'like created_at,'' the_update_at,as the name of the city,'' as service_listing_idFROMwiki_city_list c participate in Propertyinfo p at c.city_id = p.cityjoin ynow_rentwiki.Property_Unitlayout Please take away p.prop_ynow_id = pu.P_Identjoin (SELECT CAST (substring_index (if (Rent> 0, Rent, RentLow), '.', 1) AS UNSIGNED) as minrent, CAST (substring_index (if (Loyer> 0, Rent, Renthigh), '.', 1 )) AS UNSIGNED) as maxrent, PRE_Ident, P_Ident, UNL_Ident, RTY_Ident from ynow_rentwiki Where property_rents P_Ident is in (9744) and (Rent! = '' Or (Rentlow! = '' And Renthigh! = ''))) As pr to pu.UNL_Ident = pr.UNL_IdentJoin point s at (p.state = s.stateid OR p.state = s.ShortCut)ORpu.Status = 'Active'and p.delete_date = '0000-00-00'BY GROUPc.city_id, p.prop_ynow_idUNIONSELECT AS''prop_ynow_id,id as a property identifier,0 as prop_add_value,t.name as a name,'' as an image,t. address as location,T. a city as a city,see ShortCut as a state,t.zip is essentially zip,CAST (REPLACE (REPLACE (t.price, '$', ''), ',', '') as UNSIGNED) although minrent,like Maxrent,t. service as a service,'' available as Hood_id,like a phone,t latitude in latitude,t. longitude as longitude,i.e. there are bathrooms in minbad,like maxbad,t. the room is like a bed,like maxbed,t.url as a URL,t.source_id as source_id,t.source_name in the view that source_name,t.addresscode as an address code,t.citycode as city code,t.ctime is ctime,i.e. paid as paid,t.similar_url, although Similar_url,t.created_at as created_at,t.updated_at as updated_at,SUBSTRING_INDEX (c.city_name, '_', 1) mainly because city_name,t.service_listing_id is essentially service_listing_idDE LBCPrimary.third_party_properties as t, LBCPrimary.wiki_city_list as, LBCPrimary.state as sORt.stadt in ('230')andAddress <> '', etc.t.city = c.city_id andc.city_state = s.stateidorder at t.ctimedescriptionLimit 46 as limit 0.50

You

Write any type of query in MySQL and get the big error “1248: Each view must have its own alias”? If so, then this is a good, reliable, and simple error that can be corrected.

How To FixMySQL “Each View Must Have Its Own Alias” Error

So you have a specific bug in MySQL. How to solve this problem?

The short answer you want is to give your subqueries an alias in your company’s SELECT statement. The alias for adding the FROM term subquery after the closing curly brace.

This is not required in 3rd party SQL, but MySQL requires you to create an alias for your current subqueries.

Derived Table Example

A Derived Chart is a SELECT subquery in the FROM clause of your main query. It helps, like a table, because it contains articles and returns rows. For example, it might be the old location of the table in some kind of FROM clause. It is often recognized as an inline view or simply as a specific subquery.

  SELECT client_ville,COUNT (*) AS order_count,SUM (order_amount) AS order_sumFROM (  SELECT o.order_id,  o.Kunde_Stadt,  o.order_amount ORDERS  INNER JOIN member c ON o.customer_id = c.customer_id)GROUP BY Customer_City;

This query finds the location, the number of orders associated with the order invoices, and the total Accounts of orders from tables of orders and customers.

The request has an outer request and a body request. An internal query is used to find data about orders and customers:

How do you fix Every derived table must have its own alias?

How do I fix them? Short answer: you really want to use aliases for your subqueries in your SELECT statement. In the subquery of the FROM clause, add a strong alias after the closing parenthesis. Various SQL vendors do not require this, but MySQL requires you to provide aliases for your subqueries along the way.

  SELECT o.order_id, o.customer_city, o.order_amountORDERSINNER JOIN customer f ON o.customer_id = c.customer_id

In this case, the data is initially taken from two tables, which are combined into a common row of work. This is a “derived table”.

The outer topic selects multiple columns prior to this subquery. Marketing information does not come from an array, but from the results of this subquery.

How do you derive a table in SQL?

A derived table is a specific table expression that appears in the FROM clause of a query. You can manipulate views if using Hug you aliases is not possible because the SQL translator processes another term if known before the alias.

  SELECT client_ville,COUNT (*) AS order_count,SUM (order_amount) AS order_sumFROM (...)GROUP BY Customer_City;

What is error 1248 in MySQL?

MYSQL ERROR 1248 (42000): Each view must have its own nickname.

It shows customer_city, two combined skills, and city groups.

  SELECT client_ville,COUNT (*) AS order_count,SUM (order_amount) AS order_sumFROM (  SELECT o.order_id,  o.Kunde_Stadt,  o.order_amount Delivery of AB from  INTERNAL customer CONNECTION c ON o.customer_id = c.customer_id)GROUP BY Customer_City;

Error 1248: Each view must have its own alias

Solution For “Every View Must Have An Alias”

The reason for this error The point is that in MySQL, every dining view (a subquery that is a SELECT query) must have an alias.

In the sample query, there was no alias for your subquery here. The alias of the absolute subquery is the same as the alias of the channel. These are the closing parentheses of the FROM clause for a general subquery. B

So, in this example, we just need to add an alias after the closing parenthesis to fix this error.

An alias can be almost anything that interests you: a single letter, a word, several letters. I often use the word “under” when it is a small query (for a subquery), and something more descriptive when it is a longer specific query.

How do you fix Every derived table must have its own alias?

How to fix it? No doubt the short answer is that you need to give your subqueries an alias in your SELECT statement. Add the alias to the subquery of the FROM clause after the closing parenthesis. Other SQL providers do not require this, but MySQL expects you to create an alias for your subqueries.

  SELECT client_ville,COUNT (*) AS order_count,SUM (order_amount) AS order_sumFROM (  SELECT o.order_id,  o.Kunde_Stadt,  o.order_amount ORDERS  INNER JOIN customer c ON o.customer_id implies c.customer_id) underGROUP BY Customer_City;

What does Every derived table must have its own alias?

Each derived bed (AKA subquery) must have all aliases. This means that each request in parentheses should be given an alias (regardless of AS) that can be used to refer to the situation in the rest of the interface request.

Notice how the word “sub” is added on the penultimate line after the parentheses are closed? This is your alias for a subquery or view. This alias is required in MySQL but ignored Other suppliers.

Updated

Are you tired of your computer running slow? Annoyed by frustrating error messages? ASR Pro is the solution for you! Our recommended tool will quickly diagnose and repair Windows issues while dramatically increasing system performance. So don’t wait any longer, download ASR Pro today!

This query should work and no longer receive the “Each table retrieved must have its own alias” error message.

Optional: Add The AS Keyword

You can add the AS keyword as this is a great optional part of adding an alias, but it’s up to you. If it still works:

  SELECT client_ville,COUNT (*) AS order_count,SUM (order_amount) AS order_sumFROM (  SELECT o.order_id,  o.Kunde_Stadt,  o.order_amount ORDERS  INTERNAL customer CONNECTION c ON o.customer_id = c.customer_id) AS-SubGROUP BY Customer_City;

You can fix this derived table alias error in the same way. Add an alias to your subquery.

Speed up your computer today with this simple download.

Error 1248 De Mysql Sql
MySQL SQL 오류 1248
Ошибка SQL SQL 1248
Błąd MySQL Sql 1248
Erreur SQL SQL 1248
MySQL-SQL-Fehler 1248
Mysql SQL-fel 1248
Mysql SQL-fout 1248
Errore SQL MySQL 1248
Erro SQL 1248 Do MySQL

Источник

Вопрос:

Я получаю сообщение об ошибке при использовании этого запроса в MySQL.

Логика запроса верна, и я пробовал ее в Oracle, и она работает нормально, но я получаю сообщение об ошибке при работе в MySQL.

Я просмотрел предыдущие вопросы о StackOverflow, но не нашел что-то, чтобы помочь мне.

Вот запрос:

select * from
(select PM.ID, PM.Name, PM.TIMEOUT, PMS.PROCESS_MONITOR_ID, PMS.PROCESS_START_DATE
from RATOR_IMP.PROCESS_MONITOR as PM
JOIN RATOR_IMP.PROCESS_MONITOR_STATISTIC as PMS
ON PM.ID = PMS.PROCESS_MONITOR_ID
WHERE PM.ENABLED=1 and (PM.NAME='SDRRATINGENGINE11' or PM.NAME='WORKFLOWENGINE1')
order by PMS.PROCESS_START_DATE desc)
limit 10000;

И вот ошибка:

Error Code: 1248. Every derived table must have its own alias
No soultion found for query

Лучший ответ:

Вам нужно предоставить псевдоним для подзапроса, например:

select * from
(select PM.ID, PM.Name, PM.TIMEOUT, PMS.PROCESS_MONITOR_ID, PMS.PROCESS_START_DATE
 from RATOR_IMP.PROCESS_MONITOR as PM
 JOIN RATOR_IMP.PROCESS_MONITOR_STATISTIC as PMS
 ON PM.ID = PMS.PROCESS_MONITOR_ID
 WHERE PM.ENABLED=1 and (PM.NAME='SDRRATINGENGINE11' or PM.NAME='WORKFLOWENGINE1')
 order by PMS.PROCESS_START_DATE desc) as s
limit 10000;

В документации,

Подзапросы являются законными в предложении FROM SELECT. Фактический синтаксис:

SELECT… FROM (подзапрос) [AS] name…

Предложение имени [AS] является обязательным, поскольку каждая таблица в предложении FROM должна иметь имя. Любые столбцы в списке выбора подзапроса должны иметь уникальные имена.

Ответ №1

Да вам нужно указать псевдоним для полученных данных

select x.* from
(select PM.ID, PM.Name, PM.TIMEOUT, PMS.PROCESS_MONITOR_ID, PMS.PROCESS_START_DATE
from RATOR_IMP.PROCESS_MONITOR as PM
JOIN RATOR_IMP.PROCESS_MONITOR_STATISTIC as PMS
ON PM.ID = PMS.PROCESS_MONITOR_ID
WHERE PM.ENABLED=1 and (PM.NAME='SDRRATINGENGINE11' or PM.NAME='WORKFLOWENGINE1')
order by PMS.PROCESS_START_DATE desc)x <-- here
limit 10000;

Источник

Summary: in this tutorial, you will learn about the MySQL derived tables and how to use them to simplify complex queries.

Introduction to MySQL derived tables

A derived table is a virtual table returned from a SELECT statement. A derived table is similar to a temporary table, but using a derived table in the SELECT statement is much simpler than a temporary table because it does not require creating the temporary table.

The term derived table and subquery is often used interchangeably. When a stand-alone subquery is used in the FROM clause of a SELECT statement, it is also called a derived table.

The following illustrates a query that uses a derived table:

Note that a stand-alone subquery is a subquery that can execute independently of the outer query.

Unlike a subquery, a derived table must have an alias so that you can reference its name later in the query. If a derived table does not have an alias, MySQL will issue the following error:

Every derived table must have its own alias.

The following illustrates the syntax of a query that uses a derived table:

SELECT 
    select_list
FROM
    (SELECT 
        select_list
    FROM
        table_1) derived_table_name
WHERE 
    derived_table_name.c1 > 0;
Code language: SQL (Structured Query Language) (sql)

A simple MySQL derived table example

The following query gets the top five products by sales revenue in 2003 from the orders and orderdetails tables in the sample database:

SELECT 
    productCode, 
    ROUND(SUM(quantityOrdered * priceEach)) sales
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2003
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;
Code language: SQL (Structured Query Language) (sql)

You can use the result of this query as a derived table and join it with the products table as follows:

SELECT 
    productName, sales
FROM
    (SELECT 
        productCode, 
        ROUND(SUM(quantityOrdered * priceEach)) sales
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2003
    GROUP BY productCode
    ORDER BY sales DESC
    LIMIT 5) top5products2003
INNER JOIN
    products USING (productCode);
Code language: SQL (Structured Query Language) (sql)

The following shows the output of the query above:

In this example:

First, the subquery is executed to create a result set or derived table.
Then, the outer query is executed that joined the top5product2003 derived table with the products table using the productCode column.

A more complex MySQL derived table example

Suppose you have to classify the customers who bought products in 2003 into 3 groups: platinum, gold, and silver. And you need to know the number of customers in each group with the following conditions:

Platinum customers who have orders with the volume greater than 100K.
Gold customers who have orders with the volume between 10K and 100K.
Silver customers who have orders with the volume less than 10K.

To form this query, you first need to put each customer into the respective group using CASE expression and GROUP BY clause as follows:

SELECT 
    customerNumber,
    ROUND(SUM(quantityOrdered * priceEach)) sales,
    (CASE
        WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
        WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
        WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
    END) customerGroup
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2003
GROUP BY customerNumber;
Code language: SQL (Structured Query Language) (sql)

The following is the output of the query:

Then, you can use this query as the derived table and perform grouping as follows:

SELECT 
    customerGroup, 
    COUNT(cg.customerGroup) AS groupCount
FROM
    (SELECT 
        customerNumber,
            ROUND(SUM(quantityOrdered * priceEach)) sales,
            (CASE
                WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
                WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
                WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
            END) customerGroup
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2003
    GROUP BY customerNumber) cg
GROUP BY cg.customerGroup;    
Code language: SQL (Structured Query Language) (sql)

The query returns the customer groups and the number of customers in each.

In this tutorial, you have learned how to use the MySQL derived tables which are subqueries in the FROM clause to simplify complex queries.

Was this tutorial helpful?

Источник