CODEWITHSUNDEEP
sqlsql-server
Starke
Starke

Reputation: 21

Sum of items not fulfilling the having by clause SQL

I have a table of production with columns code, name, country, production

I need to list countries that are exceeding the production of 1000 and also make a sum of production of all other countries and label it other. So the output will look like this

Country   production
-------   ----------
Japan     1380
England   1400
Other     2700

Is it possible to make this without union in one select?

Upvotes: 2

Views: 88

Answers (2)

Filip De Vos
Filip De Vos

Reputation: 11908

Since you want to return all the data, but grouped differently you don't need to look at this task as a "Filtering" exercise, but only as a grouping/re-labeling exercise. To relabel everything with a production > 1000 you can use the case statement. All that is left to do is grouping. No need for any sub-queries.

  SELECT country = CASE WHEN production >= 1000 
                        THEN country 
                        ELSE 'Other' 
                   END 
       , production = SUM(production)
    FROM production
GROUP BY CASE WHEN production >= 1000 
              THEN country 
              ELSE 'Other' 
         END

If the table can contain multiple records per country, the answer of @niktrs is correct.

Upvotes: 1

niktrs
niktrs

Reputation: 10066

Using a subquery:

SELECT CASE WHEN production >= 1000 THEN country ELSE 'Other' END country
     , SUM(production) AS production
FROM(
    SELECT country, 
           SUM(production) AS production
    FROM production
    GROUP BY country
) AS a
GROUP BY CASE WHEN production >= 1000 THEN country ELSE 'Other' END

Upvotes: 4

Related Questions