mysql tips - JOIN and GROUP BY - complex query example


SELECT td.c1, td.c2, sum(tc.c3) FROM
  (SELECT tc.*, tb.c1, tb.c2 FROM tb
      JOIN (SELECT ta.c1, ta.c2, sum(ta.c3) FROM ta WHERE... GROUP BY...) tc
      ON  tb.c4=ta.c5) td
GROUP BY td.c1, td.c2

Comments

Post a Comment

Popular posts from this blog

install ipython in Cloudera VM using pip

I am practicing pyspark in Cloudera VM, and pyspack need be launched by ipython. In all the old Cloudera VM guide, it use easy_install to install ipython, but it does not work in Virtualbox 5.2.0 r118431 and later. And will get the error : $sudo easy_install ipython==1.2.1 ... error: Could not find suitable distribution for Requirement.parse('ipython==1.2.1') The solution for this is to use pip to install ipython. But by default, pip is not installed in cloudera VM, and pip can not be installed by eacy_install as well. And after some search, I  noticed cloudera has yum installed, and yum can install pip: $sudo yum install python-pip ... $which pip /usr/bin/pip Now ipython can be installed b pip: $sudo pip install ipython=1.2.1 ... Succesfully installled ipython-1.2.1 After that, launch spack by ipython: $PYSPARK_DRIVER_PYTHON=ipython pyspark ... Welcome to       ____                      ...
Keep reading

install postgreSQL in ubuntu 16.04

When following the Install PostgreSQL in Ubuntu  in Ubuntu 16.04, got error as below: $ apt-get install postgresql-9.6 Reading package lists... Done Building dependency tree Reading state information... Done You might want to run 'apt-get -f install' to correct these: The following packages have unmet dependencies: adobereader-enu:i386 : Depends: libgtk2.0-0:i386 (>= 2.4) but it is not going to be installed postgresql-9.6 : Depends: postgresql-client-9.6 Depends: postgresql-common (>= 171~) but it is not going to be installed Recommends: postgresql-contrib-9.6 but it is not going to be installed E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a solution). The suggested solution in the last line and a normal ubuntu upgrade do not solve the issue,  and the cmd below can fix it: $sudo apt full-upgrade After the fix, then can apt-get to install postgresql again. After install postgresql, ...
Keep reading

Install MYSQL 5.6 in ubuntu 12.04/14.04 and set password and remote access

Part 1 : The steps to install mysql 5.6 in ubuntu 12.04 Note: it is also possible that , when you install mysql, there is a pop-up window to ask you to enter the password for root, and you/'d better remember  that in case there will be some extra work to set password in the Part 2. 1. check the system OS version uname -i 2.If there is an old mysql version running on your system and you need to uninstall it, and if you also install a .deb version, you need to use the "dpkg" one to uninstall: sudo apt-get remove mysql-server mysql-server-5.5 mysql-server-core-5.5 sudo apt-get remove --purge mysql-server mysql-client mysql- common sudo dpkg --pure mysql-server mysql-client mysql-common sudo apt-get autoremove sudo apt-get autoclean deluser mysql sudo updatedb ##sometimes after above cmds,using "locate mysql" still lists many file related to mysql, that is because the system database is not updated## sudo find /var/lib/mysql/ -type f -delete ##delete ...
Keep reading