clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591659 fiddles created (45713 in the last week).

CREATE TABLE a1111 ([PMM] int, [ORDER_NO] varchar(6), [MKQTY] int) ; INSERT INTO a1111 ([PMM], [ORDER_NO], [MKQTY]) VALUES (10905, '4N0241', 250), (10905, '4N0301', 375), (10905, '4N0292', 3700), (10905, '4N0331', 1335), (10905, '4N0341', 365), (10906, '5N3234', 1425), (10906, '5N3245', 7400), (10906, '5N0271', 1075), (10906, '5N0284', 1200), (10906, '5N3251', 4000), (10907, '6N3191', 2800), (10907, '6N3103', 8800), (10907, '6N0371', 1200), (10907, '6N3194', 1540), (10907, '6N3104', 2250), (10908, '7N3229', 10160), (10908, '7N0202', 1025), (10908, '7N0251', 6600), (10908, '7N0221', 1400), (10908, '7N3283', 3400), (10908, '8N3215', 1890), (10908, '8N3222', 9525), (10908, '8N0151', 7575), (10909, '8N0121', 11550), (10909, '9N3011', 7900), (10909, '8N0121', 660), (10909, '8N0201', 1515), (10909, '9N3011', 2600), (10909, '8N0211', 295), (10909, '9N3012', 10500), (10909, '8N3231', 8230), (10909, '9N3013', 2250), (10909, '9N3014', 2650), (10910, '9N0131', 7000), (10910, '9N0341', 5170), (10910, '9N3244', 1535), (10910, '9N3245', 1235), (10911, 'AN3226', 715), (10911, 'AN3223', 695), (10911, 'AN3251', 6015), (10911, 'AN3197', 4600), (10911, 'AN3222', 685), (10911, 'AN3231', 1535), (10912, 'BN3223', 1825), (10912, 'BN3227', 1535), (10912, 'BN3226', 5075), (10912, 'BN0291', 200), (10912, 'BN3224', 325), (10912, 'BN3221', 1115), (10912, 'BN3225', 1550), (10912, 'BN3244', 525), (11001, 'CN0031', 4000), (11001, 'CN0301', 600), (11001, 'CN0291', 4200), (11001, 'CN3154', 1550), (11001, 'CN0291', 6000), (11001, 'CN3134', 2125), (11001, 'CN0031', 6000), (11002, '1N0271', 10000), (11002, '1N0264', 3700), (11002, '1N3192', 10280), (11002, '1N0267', 1925), (11002, '1N0264', 1500), (11002, '1N0265', 1910), (11002, '1N0282', 1625), (11002, '1N3244', 300), (11002, '1N3248', 1075), (11003, '2N3196', 2800), (11003, '2N3201', 950), (11003, '2N0271', 1690), (11003, '2N3197', 9975), (11003, '2N0243', 225), (11004, '3N3123', 16800), (11004, '3N3124', 3900), (11004, '3N3121', 19800), (11004, '3N3161', 7500), (11004, '3N3161', 5425), (11004, '3N3122', 4600), (11004, '3N3124', 6300), (11004, '3N3121', 1800), (11004, '3N3161', 10375), (11004, '3N3122', 12100), (11005, '4N3197', 2908), (11005, '4N3196', 250), (11005, '4N3194', 10000), (11005, '4N3195', 1950), (11005, '5N3021', 1025), (11005, '4N3192', 100), (11005, '4N3191', 5000), (11005, '4N3211', 1765), (11005, '5N0011', 2045), (11005, '4N3192', 4200), (11005, '4N3193', 5100), (11005, '4N3192', 700), (11006, '5N3291', 4850), (11006, '5N0181', 1325), (11006, '6N3011', 10327), (11006, '5N3243', 3900), (11006, '5N3302', 8800), (11006, '6N3021', 3850), (11006, '5N3243', 6075), (11006, '5N3302', 8895), (11006, '5N0171', 5225), (11006, '6N3031', 300), (11006, '6N3021', 1300) ;
105 rows affected
 hidden batch(es)


SELECT PMM, COUNT(*) '總訂單筆數', COUNT(CASE WHEN MKQTY <= 500 THEN 1 END) '500 pc以下', COUNT(CASE WHEN MKQTY BETWEEN 501 AND 999 THEN 1 END) '501~1000pc(含)', COUNT(CASE WHEN MKQTY >= 1000 THEN 1 END) '1000 pc以上', COUNT(CASE WHEN [PMM] >= 10907 THEN 1 END) '前12個月' FROM a1111 GROUP BY PMM
PMM 總訂單筆數 500 pc以下 501~1000pc(含) 1000 pc以上 前12個月
10905 5 3 0 2 0
10906 5 0 0 5 0
10907 5 0 0 5 5
10908 8 0 0 8 8
10909 10 1 1 8 10
10910 4 0 0 4 4
10911 6 0 3 3 6
10912 8 2 1 5 8
11001 7 0 1 6 7
11002 9 1 0 8 9
11003 5 1 1 3 5
11004 10 0 0 10 10
11005 12 2 1 9 12
11006 11 1 0 10 11
Warning: Null value is eliminated by an aggregate or other SET operation.
 hidden batch(es)