SQL 髢「謨ーシ壹げ繝ォ繝シ繝鈴未謨ー

讎りヲ

繧ー繝ォ繝シ繝怜喧縺励◆隍謨ー陦後ョ繝繝シ繧ソ縺ォ蟇セ縺励※蜊倅ク縺ョ蛟、繧定ソ斐☆髢「謨ー
窶サ蜊倅ク陦碁未謨ーシ晢シ題。後ョ繝繝シ繧ソ縺ォ蟇セ縺励※蜊倅ク縺ョ蛟、繧定ソ斐☆
繧ー繝ォ繝シ繝怜喧縺ォ縺、縺縺ヲ縺ッ縲SQL DMLシ夐寔險縲榊盾辣ァ

窶サ莉・荳九ョ繧ー繝ォ繝シ繝鈴未謨ー縺ァ繧ー繝ォ繝シ繝怜喧繧定。後≧陦ィ

ID NAME DATE
1 GROUP-1 99-01-01
2 GROUP-1 99-01-01
3 GROUP-1 99-01-02
4 GROUP-1 99-01-03
5 GROUP-1 99-01-04
6 GROUP-1 99-01-05
7 GROUP-2 99-02-01
8 GROUP-2 99-02-02
9 GROUP-2 99-02-03
10 GROUP-2 99-02-04
11 GROUP-2 99-02-05
12 GROUP-2 99-02-06
荳隕ァ

髢「謨ー 蜿門セ * 謨ー蛟、 譁蟄 譌・莉
COUNT 繝繝シ繧ソ莉カ謨ー
MIN 譛蟆
MAX 譛螟ァ
AVG 蟷ウ蝮
SUM 蜷郁ィ
STDDEV 讓呎コ門¥蟾ョ
VARANCE 豈埼寔蝗」讓呎コ門¥蟾ョ

COUNT

SELECT
縲縲COUNT(ID) AS COUNT,
縲縲TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);
COUNT縲YYYY/MM
シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ
6縲1999/02
6縲1999/01

SUM

SELECT
縲縲SUM(ID),
縲縲TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);
SUM縲YYYY/MM
シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ
57縲1999/02
21縲1999/01

MAX/MIN

SELECT
縲縲MAX(ID),
縲縲MIN(ID),
縲縲TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);

MAX縲 MIN縲YYYY/MM
シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ
12縲 7縲1999/02
66縲 1縲1999/01

AVG

蟷ウ蝮
SELECT
縲縲AVG(ID) AS AVG,
縲縲TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);

AVG縲YYYY/MM
シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ
ス槭1999/01

STDDEV

讓呎コ門¥蟾ョ
SELECT
縲縲STDDEV(ID) AS STDDEV,
縲縲TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);

STDDEV縲YYYY/MM
シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ
ス槭1999/01

蜈ィ蛻玲欠螳(*)

蜈ィ縺ヲ縺ョ蛻励〒菴ソ逕ィ蜿ッ閭ス
SELECT COUNT(*)
SELECT COUNT('X')
FROM 陦ィ蜷
WHERE ス;

NULL縺ョ髮險

繝繝シ繧ソ蝙 NULL
MIN (蛻怜錐) 謨ー蛟、縲∵枚蟄励∵律莉 辟。隕
MAX (蛻怜錐) 謨ー蛟、縲∵枚蟄励∵律莉 辟。隕
COUNT (蛻怜錐) 謨ー蛟、縲∵枚蟄励∵律莉 辟。隕 COUNT(*) シ哢ULL縺碁寔險医&繧後k
AVG (蛻怜錐) 謨ー蛟、 辟。隕
SUM (蛻怜錐) 謨ー蛟、 辟。隕

ID蛻暦シ1,1,(NULL),2,2 縺ョ蝣エ蜷
COUNT(ALL ID) = 4
窶サ(NULL)縺ッ髮險医&繧後↑縺
COUNT(ID) シ 4
窶サALL縺ィ蜷後§
COUNT(DISTINCT ID) シ 2
COUNT(*) = 5
窶サ(NULL)繧る寔險医☆繧

NULL縺ョ蛻、螳/鄂ョ謠帙ッ縲SQL 螟画鋤髢「謨ーNULL蛟、縺ョ蛻、螳壹サ螟画鋤縲榊盾辣ァ

繧ー繝ォ繝シ繝鈴未謨ー縺悟茜逕ィ蜿ッ閭ス縺ェ蜿・

繝サSELECT蜿・
SELECT
縲COUNT(ス)
FROM ス

繝サHAVING蜿・
SELECT ス FROM ス GROUP BY ス
HAVING COUNT(ス) = ス

繝サORDER BY蜿・
SELECT ス FROM ス
ORDER BY COUNT(ス)

Follow me!