Skip to content

Instantly share code, notes, and snippets.

@ryuichimatsumoto-single
Created September 9, 2017 12:28
Show Gist options
  • Save ryuichimatsumoto-single/40209d7daacbd65fd87cc61020e6218e to your computer and use it in GitHub Desktop.
Save ryuichimatsumoto-single/40209d7daacbd65fd87cc61020e6218e to your computer and use it in GitHub Desktop.
Make histgram from SQL for servey sales stetement
select (@number := 0 ) AS min, (@number := @number + 200) AS max,count(*) as cnt,sum(AAA) as sum from WWW where XXX IN (YY,YY) and AAA between 0 and 200 and BBB between "2017-07-20 00:00:00" and "2017-08-17 23:59:59"
UNION select (@number := 200 ) AS min, (@number := @number + 200) AS max,count(*) as cnt,sum(AAA) as sum from WWW where XXX IN (YY,YY) and AAA between 201 and 400 and BBB between "2017-07-20 00:00:00" and "2017-08-17 23:59:59"
UNION select (@number := 400 ) AS min, (@number := @number + 200) AS max,count(*) as cnt,sum(AAA) as sum from WWW where XXX IN (YY,YY) and AAA between 401 and 600 and BBB between "2017-07-20 00:00:00" and "2017-08-17 23:59:59"
UNION select (@number := 600 ) AS min, (@number := @number + 200) AS max,count(*) as cnt,sum(AAA) as sum from WWW where XXX IN (YY,YY) and AAA between 601 and 800 and BBB between "2017-07-20 00:00:00" and "2017-08-17 23:59:59"
UNION select (@number := 800 ) AS min, (@number := @number + 200) AS max,count(*) as cnt,sum(AAA) as sum from WWW where XXX IN (YY,YY) and AAA between 801 and 1000 and BBB between "2017-07-20 00:00:00" and "2017-08-17 23:59:59"
UNION select (@number := 1000 ) AS min, (@number := @number + 200) AS max,count(*) as cnt,sum(AAA) as sum from WWW where XXX IN (YY,YY) and AAA between 1001 and 1200 and BBB between "2017-07-20 00:00:00" and "2017-08-17 23:59:59"
UNION select (@number := 1200 ) AS min, (@number := @number + 200) AS max,count(*) as cnt,sum(AAA) as sum from WWW where XXX IN (YY,YY) and AAA between 1201 and 1400 and BBB between "2017-07-20 00:00:00" and "2017-08-17 23:59:59"
UNION select (@number := 1400 ) AS min, (@number := @number + 200) AS max,count(*) as cnt,sum(AAA) as sum from WWW where XXX IN (YY,YY) and AAA between 1401 and 1600 and BBB between "2017-07-20 00:00:00" and "2017-08-17 23:59:59"
UNION select (@number := 1600 ) AS min, (@number := @number + 200) AS max,count(*) as cnt,sum(AAA) as sum from WWW where XXX IN (YY,YY) and AAA between 1601 and 1800 and BBB between "2017-07-20 00:00:00" and "2017-08-17 23:59:59"
UNION select (@number := 1800 ) AS min, (@number := @number + 200) AS max,count(*) as cnt,sum(AAA) as sum from WWW where XXX IN (YY,YY) and AAA between 1801 and 2000 and BBB between "2017-07-20 00:00:00" and "2017-08-17 23:59:59"
UNION select (@number := 2000 ) AS min, (@number := @number + 200) AS max,count(*) as cnt,sum(AAA) as sum from WWW where XXX IN (YY,YY) and AAA between 2001 and 2200 and BBB between "2017-07-20 00:00:00" and "2017-08-17 23:59:59"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment