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

Qlikview tips - how to escape characters and space LOAD INLINE table

When you load a table using LOAD * INLINE, and need a space at the end of a string. Then you will find it is impossible to have a space like below in the red highlight part: LOAD * INLINE [ FieldName A   A-B-C B ]; And you can get it by using replace() function like below: LOAD Replace(FieldName,'$',' ' )as FieldName  INLINE [ FieldName A $ A-B-C B ]; This replace() function can also be used to escape "]" or other characters. Really handy and smart design.
Keep reading