0

SQL 計算同一個數值再整個table 出現的次數

SELECT COUNT(CASE WHEN name = "green" then 1 else NULL end) AS green,
COUNT(CASE WHEN name = "yellow" then 1 else NULL end) AS yellow
FROM T1

(ps 我是要在 confluence 上使用)

2 個回答

0

iT邦超人 1 級 ‧ 2020-09-18 17:28:51

sql資料

``````CREATE TABLE `ggg` (
`num` int(11) NOT NULL,
`name` varchar(1) NOT NULL,
`age` varchar(1) NOT NULL,
`car` varchar(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `ggg` (`num`, `name`, `age`, `car`) VALUES
(1, 'G', 'Y', 'G'),
(2, 'Y', 'G', 'Y'),
(3, 'R', 'W', 'G'),
(4, 'G', 'G', 'Y');
``````

``````SELECT
sum(if(name='G',1,0)+if(age='G',1,0)+if(car='G',1,0)) AS Gnum,
sum(if(name='Y',1,0)+if(age='Y',1,0)+if(car='Y',1,0)) AS Ynum
FROM `ggg`
``````

``````SELECT a,count(*) AS num FROM (
SELECT name AS a FROM `ggg` AS n
UNION ALL
SELECT age AS a FROM `ggg` AS a
UNION ALL
SELECT car AS a FROM `ggg` AS c
) AS t GROUP by a
``````

1

iT邦大師 1 級 ‧ 2020-09-18 18:12:56

``````create table it200918d (
id int not null
, c1 text not null
, c2 text not null
, c3 text not null
);

insert into it200918d values
(1, 'G', 'Y', 'G'),
(2, 'Y', 'G', 'Y'),
(3, 'R', 'W', 'G'),
(4, 'G', 'G', 'Y');

select ca
, count(*)
from (select unnest(array[c1, c2, c3]) ca
from it200918d) a
group by ca;

+----+-------+
| ca | count |
+----+-------+
| Y  |     4 |
| R  |     1 |
| W  |     1 |
| G  |     6 |
+----+-------+
(4 rows)

``````