下面是一組資料
SELECT '2019-01' AS Month, 'YT' AS BIZ, 'AP' AS Region, 100.90 AS Revenue
INTO T
UNION ALL
SELECT '2019-01' AS Month, 'ZY' AS BIZ, 'AP' AS Region, 200.90 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'AIO' AS BIZ, 'AP' AS Region, 300 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'XY' AS BIZ, 'AP' AS Region, 400 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'AA' AS BIZ, 'AP' AS Region, 250 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'BB' AS BIZ, 'AP' AS Region, 900 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'XY' AS BIZ, 'EMEA' AS Region, 400 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'AA' AS BIZ, 'EMEA' AS Region, 250 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'BB' AS BIZ, 'EAEA' AS Region, 900 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'AIO' AS BIZ, 'AP' AS Region, 300 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'XY' AS BIZ, 'AP' AS Region, 400 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'AA' AS BIZ, 'AP' AS Region, 250 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'BB' AS BIZ, 'AP' AS Region, 900 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'AIO' AS BIZ, 'EMEA' AS Region, 200 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'XY' AS BIZ, 'EMEA' AS Region, 100 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'AA' AS BIZ, 'EMEA' AS Region, 700 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'BB' AS BIZ, 'EMEA' AS Region, 300 AS Revenue
UNION ALL
SELECT '2019-03' AS Month, 'AIO' AS BIZ, 'AP' AS Region, 300 AS Revenue
UNION ALL
SELECT '2019-03' AS Month, 'XY' AS BIZ, 'AP' AS Region, 400 AS Revenue
UNION ALL
SELECT '2019-03' AS Month, 'AA' AS BIZ, 'AP' AS Region, 250 AS Revenue
UNION ALL
SELECT '2019-03' AS Month, 'BB' AS BIZ, 'AP' AS Region, 900 AS Revenue
建表以后，
第一題我想知道每個月所有區域的總值和平均值。終表需要月，總值，平均值。這里的平均值是每個月都是同一個數
第二題求月，地區，總值，平均值（相同區域跨月的平均）
上面的表格是第一題期待的結果，下面的表格是第二題期待的結果
謝謝
uj5u.com熱心網友回復：
第一題已經解出來了select month, sum(revenue) as revenue,
(select sum(revenue)/3 from T) as avg_revune from T
group by month
純是小白自學，如果問題太幼稚，還請大家海涵，希望得到各位的幫助。
第二題還在醞釀中。
uj5u.com熱心網友回復：
建表的資料可能跟最后表格里的資料有點不同，但是想要的結果大致類似的意思。第二題，我覺得是否應該用sum(revenue)/count(*)解決每個region的月平均值呢？
可是我解決的方法可能不對，應該count group by 以后的region數量，不是原始表中region的數量。
uj5u.com熱心網友回復：
哪位大神能幫我解決一下？轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/49539.html
標籤：疑難問題