How to aggregate multiple outcome of case condition in sql?-CodePudding

Here is a sample code:

SELECT DISTINCT salary, planet,
              sum(case when company LIKE '%Google%' then 1 end) `Google`,
              sum(case when company LIKE '%IBM%' then 1 end) `IBM`,
              sum(case when company LIKE '%Cisco%' then 1 end) `Cisco` 
from industries
where planet = 'Earth' ;

Can someone give me advice how to summarize amount of multiple variables defined outside case condition? I tried to use simple math, but it did not work.

    SELECT DISTINCT salary, planet,
                  sum(case when company LIKE '%Google%' then 1 end) `Google`,
                  sum(case when company LIKE '%IBM%' then 1 end) `IBM`,
                  sum(case when company LIKE '%Cisco%' then 1 end) `Cisco`,
  -- similar math to count multiple  columns, 
                  sum(`Google`    `IBM`   `Cisco`) AS Total_amount
    
    from industries
    where planet = 'Earth' ;

The result should like this:

------------------------------------------------------------
| salary  | Planet| Google |  IBM |  Cisco |  Total_amount |
|----------------------------------------------------------|
| 3000.00 | Earth |   26   |   26 |    25  |      77       |
------------------------------------------------------------

Thanks in advance!

CodePudding user response：

It's just in front of your eyes. It happens to me, too. Just COUNT(*) for the total amount.

WITH industries(salary,planet,company) AS (
            SELECT 3000.00,'Earth','Google'
  UNION ALL SELECT 3000.00,'Earth','Google'
  UNION ALL SELECT 3000.00,'Earth','Google'
  UNION ALL SELECT 3000.00,'Earth','Google'
  UNION ALL SELECT 3000.00,'Earth','IBM'
  UNION ALL SELECT 3000.00,'Earth','IBM'
  UNION ALL SELECT 3000.00,'Earth','IBM'
  UNION ALL SELECT 3000.00,'Earth','Cisco'
  UNION ALL SELECT 3000.00,'Earth','Cisco'
  UNION ALL SELECT 3000.00,'Earth','Cisco'
  UNION ALL SELECT 3000.00,'Earth','Cisco'
  UNION ALL SELECT 3000.00,'Earth','Cisco'
  UNION ALL SELECT 3000.00,'Earth','Cisco'
)
SELECT
  salary
, planet
, SUM(CASE company WHEN 'Google' THEN 1 END) AS Google
, SUM(CASE company WHEN 'IBM'    THEN 1 END) AS IBM 
, SUM(CASE company WHEN 'Cisco'  THEN 1 END) AS Cisco
, COUNT(*)                                   AS total_amount
FROM industries
WHERE planet = 'Earth'
GROUP BY
  salary
, planet
;
-- out  salary  | planet | Google | IBM | Cisco | total_amount 
-- out --------- -------- -------- ----- ------- --------------
-- out  3000.00 | Earth  |      4 |   3 |     6 |           13

CodePudding user response：

Consider below (BigQuery)

SELECT salary, planet, 
  COUNTIF(company LIKE '%Google%') AS Google, 
  COUNTIF(company LIKE '%IBM%') AS IBM , 
  COUNTIF(company LIKE '%Cisco%') AS Cisco, 
  COUNTIF(REGEXP_CONTAINS(company, 'Google|IBM|Cisco')) AS Total_amount
FROM industries
WHERE planet = 'Earth'
GROUP BY salary, planet

CodePudding user response：

Yet another approach (BigQuery)

SELECT *, Google   IBM   Cisco AS Total_amount 
FROM (
  SELECT * EXCEPT(company), 
    REGEXP_EXTRACT(company, 'Google|IBM|Cisco') as col
  FROM industries
  WHERE planet = 'Earth'
)
PIVOT (COUNT(*) FOR col IN ('Google','IBM','Cisco'))