###前提・実現したいこと
1月から12月の売上データで月次集計を行いたいです。
現在、集計はできているのですが、見た目の部分で躓いております。
下記の画像は現状です。
教授の程、宜しくお願い申し上げます。
###該当のソースコード
sales_order_id,seller_id,keyboarder_id,account_type_id,processing_status_id,selling_party_id,orderer_id,buyer_id,point_of_sale_id,sales_method_id,payment_method_id,customer_group_id,number_of_customer,consumption_tax_rate,free_tax_rate,use_points,usually_grand_total,custom_grand_total,grand_consumption_tax_total,reward_points,payment_confirm 1,1,1,1,2,2,3,3,5,1,28,3,1,0,8,0,0,32000,0,320,2017-06-01 2,1,1,2,2,2,1,1,5,1,29,3,1,0,8,0,0,3700,0,37,2017-06-01 3,1,1,1,2,2,2,2,5,1,28,2,1,0,8,0,0,60000,0,600,2017-06-01 4,1,1,2,2,2,1,1,5,1,29,2,1,0,8,0,0,4100,0,41,2017-06-01 5,1,1,1,2,3,3,2,4,1,17,2,1,8,0,0,29000,0,2320,290,2017-06-01 6,1,1,2,2,3,1,1,4,1,19,2,1,8,0,0,600,0,48,6,2017-06-01 7,1,1,1,2,2,2,2,4,1,20,2,1,8,0,0,62000,0,4960,620,2017-06-01 8,1,1,1,2,3,3,3,4,1,15,4,1,8,0,0,25500,0,2040,255,2017-06-01 9,1,1,1,2,3,3,2,2,1,1,2,1,8,0,0,9259,0,741,185,2017-06-01 10,1,1,1,2,2,2,2,5,1,28,2,1,0,8,0,0,8000,0,80,2017-06-02 11,1,1,2,2,2,1,1,5,9,29,2,1,0,8,0,0,1400,0,14,2017-06-02 12,1,1,1,2,2,2,2,5,1,26,1,1,0,8,0,0,70000,0,700,2017-06-02 13,1,1,1,2,3,3,2,5,1,28,2,1,0,8,1588,0,66212,0,662,2017-06-02 14,1,1,1,2,3,3,3,2,1,1,2,1,8,0,7500,40500,0,3240,810,2017-06-02 324,6,6,1,2,3,3,3,4,1,15,2,1,8,0,0,23000,0,1840,230,2017-07-01 325,6,6,1,2,2,2,2,5,1,28,2,1,0,8,0,0,48000,0,480,2017-07-01 326,6,6,2,2,2,1,1,5,9,29,2,1,0,8,0,0,2700,0,27,2017-07-01 327,6,6,1,2,2,2,2,2,1,1,3,1,8,0,0,16000,0,1280,320,2017-07-01 328,6,6,1,2,2,2,2,2,1,3,2,1,8,0,0,116950,0,9356,2339,2017-07-01 329,6,6,1,2,3,3,3,4,2,17,2,1,8,0,650,81350,0,6508,814,2017-07-01 330,6,6,1,2,3,3,3,4,9,19,2,1,8,0,0,600,0,48,6,2017-07-01 331,6,6,1,2,3,3,3,2,8,1,4,1,8,0,0,18000,0,1440,360,2017-07-01 332,6,6,1,2,3,3,2,2,8,7,2,1,8,0,0,29815,0,2385,596,2017-07-01 333,6,6,1,2,3,3,2,2,4,7,2,1,8,0,0,20426,0,1634,409,2017-07-01 348,6,6,1,2,3,3,3,4,1,15,2,1,8,0,0,92500,0,7400,925,2017-07-02 349,6,6,1,2,2,2,2,4,1,15,2,1,8,0,0,16667,0,1333,167,2017-07-02 350,6,6,1,2,3,3,3,4,1,15,2,1,8,0,0,75000,0,6000,750,2017-07-02 351,6,6,1,2,2,2,2,4,4,15,2,1,8,0,500,39584,0,3167,396,2017-07-02 352,6,6,1,2,2,2,2,2,1,1,2,1,8,0,0,17000,0,1360,340,2017-07-02 353,6,6,1,2,2,2,2,2,1,1,2,1,8,0,0,12600,0,1008,252,2017-07-02 354,6,6,1,2,3,3,2,4,1,15,2,1,8,0,0,105000,0,8400,1050,2017-07-02 615,5,5,2,2,3,1,1,1,9,27,2,1,0,0,0,0,0,0,0,2017-08-10 616,1,1,1,2,2,2,2,2,1,1,2,1,8,0,0,23000,0,1840,460,2017-08-10 617,1,1,1,2,2,2,2,2,1,1,2,1,8,0,2761,392258,0,31381,7845,2017-08-11 618,1,1,1,2,2,2,2,2,6,3,2,1,8,0,0,200000,0,16000,4000,2017-08-11 619,1,1,1,2,3,3,2,5,1,26,2,1,0,8,1352,0,26648,0,266,2017-08-11 620,1,1,1,2,3,3,3,5,1,26,2,1,0,8,0,0,27000,0,270,2017-08-11 621,1,1,1,2,3,1,1,5,9,27,2,1,0,8,0,0,3300,0,33,2017-08-11 622,1,1,1,2,2,2,2,4,6,15,2,1,8,0,373,51627,0,4130,520,2017-08-11 623,2,2,1,2,3,3,2,2,1,3,3,1,8,0,0,140000,0,11200,2800,2017-08-11 624,4,2,1,2,3,3,2,2,1,1,2,1,8,0,0,21000,0,1680,420,2017-08-11 625,2,2,1,2,3,3,2,2,1,1,2,1,8,0,0,74074,0,5926,1481,2017-08-11 626,2,2,1,2,3,3,3,2,1,1,1,1,8,0,40,5460,0,437,109,2017-08-11 627,4,2,1,2,3,3,3,2,2,3,3,1,8,0,0,18000,0,1440,360,2017-08-11 628,4,2,1,2,3,3,2,2,1,1,2,1,8,0,600,7900,0,632,158,2017-08-11 629,2,2,1,2,3,3,2,2,1,3,2,1,8,0,0,41000,0,3280,820,2017-08-11 630,1,1,1,2,2,2,2,2,1,1,2,1,8,0,0,625269,0,50022,12505,2017-08-11 631,1,1,1,2,2,2,2,4,1,15,2,1,8,0,0,48000,0,3840,480,2017-08-12 632,1,1,1,2,2,2,2,4,1,22,2,1,8,0,0,13300,0,1064,133,2017-08-12 633,1,1,1,2,2,2,2,5,1,28,2,1,0,8,664,0,69336,0,693,2017-08-12 634,1,1,2,2,2,1,1,5,9,29,2,1,0,8,0,0,3700,0,37,2017-08-12
SQL
1SELECT 2 DATE_FORMAT( R.payment_confirm , '%Y-%m' ) AS '年月' 3 -- F社 4 , IFNULL(SUM(R.usually_grand_total * (R.point_of_sale_id = '2') * (R.orderer_id = '2')) + SUM(R.custom_grand_total * (R.point_of_sale_id = '3') * (R.orderer_id = '2')) + SUM(R.grand_consumption_tax_total * (R.point_of_sale_id = '2') * (R.orderer_id = '2')), 0) AS 'F社 - 店舗' 5 , IFNULL(SUM(R.usually_grand_total * (R.point_of_sale_id = '4') * (R.orderer_id = '2')) + SUM(R.grand_consumption_tax_total * (R.point_of_sale_id = '4') * (R.orderer_id = '2')), 0) AS 'F社 - 国内通販' 6 , IFNULL(SUM(R.custom_grand_total * (R.point_of_sale_id = '5') * (R.orderer_id = '2')), 0) AS 'F- 海外通販' 7 , IFNULL(SUM(R.usually_grand_total * (R.orderer_id = '2')) + SUM(R.custom_grand_total * (R.orderer_id = '2')) + SUM(R.grand_consumption_tax_total * (R.orderer_id = '2')), 0) AS 'F社 - 合計' 8 -- R社 9 , IFNULL(SUM(R.usually_grand_total * (R.point_of_sale_id = '2') * (R.orderer_id = '3')) + SUM(R.custom_grand_total * (R.point_of_sale_id = '3') * (R.orderer_id = '3')) + SUM(R.grand_consumption_tax_total * (R.point_of_sale_id = '2') * (R.orderer_id = '3')), 0) AS 'R社 - 店舗' 10 , IFNULL(SUM(R.usually_grand_total * (R.point_of_sale_id = '4') * (R.orderer_id = '3')) + SUM(R.grand_consumption_tax_total * (R.point_of_sale_id = '4') * (R.orderer_id = '3')), 0) AS 'R社 - 国内通販' 11 , IFNULL(SUM(R.custom_grand_total * (R.point_of_sale_id = '5') * (R.orderer_id = '3')), 0) AS 'R社 - 海外通販' 12 , IFNULL(SUM(R.usually_grand_total * (R.orderer_id = '3')) + SUM(R.custom_grand_total * (R.orderer_id = '3')) + SUM(R.grand_consumption_tax_total * (R.orderer_id = '3')), 0) AS 'R社 - 合計' 13 -- C社 14 , IFNULL(SUM(R.usually_grand_total * (R.point_of_sale_id = '2') * (R.orderer_id = '4')) + SUM(R.custom_grand_total * (R.point_of_sale_id = '3') * (R.orderer_id = '4')) + SUM(R.grand_consumption_tax_total * (R.point_of_sale_id = '2') * (R.orderer_id = '4')), 0) AS 'C社 - 店舗' 15 , IFNULL(SUM(R.usually_grand_total * (R.point_of_sale_id = '4') * (R.orderer_id = '4')) + SUM(R.grand_consumption_tax_total * (R.point_of_sale_id = '4') * (R.orderer_id = '4')), 0) AS 'C社 - 国内通販' 16 , IFNULL(SUM(R.custom_grand_total * (R.point_of_sale_id = '5') * (R.orderer_id = '4')), 0) AS 'C社 - 海外通販' 17 , IFNULL(SUM(R.usually_grand_total * (R.orderer_id = '4')) + SUM(R.custom_grand_total * (R.orderer_id = '4')) + SUM(R.grand_consumption_tax_total * (R.orderer_id = '4')), 0) AS 'C社 - 合計' 18FROM 19 vw_digits as D 20LEFT JOIN 21 ( SELECT * FROM vw_result ) R 22ON 23 ADDDATE(DATE_FORMAT( R.payment_confirm , '%Y-%m-01' ), D.number) = DATE(R.payment_confirm) 24GROUP BY DATE_FORMAT( R.payment_confirm , '%Y-%m' );
###補足情報(言語/FW/ツール等のバージョンなど)
MySQL
回答1件
あなたの回答
tips
プレビュー