Category Archives: MySQL

[MySQL] Calculating Simple Subtotals

Leave a reply

Use GROUP BY’s ROLLUP extension to produce a grand total for all salaries
along with the subtotals for each JOB:

Use the COEALESCE function against the JOB column. If the value of JOB is
NULL, the value for SAL is the grand total created by ROLLUP. If the value of JOB is not NULL,
the value for SAL is the result of the “regular” GROUP BY, not the ROLLUP:

방법1)

mysql> select coalesce(job, 'TOTAL') job, sum(sal) sal
    -> from EMP
    -> group by job with rollup;
+-----------+-------+
| job       | sal   |
+-----------+-------+
| ANALYST   |  6000 |
| CLERK     |  6850 |
| MANAGER   |  9825 |
| PRESIDENT |  4000 |
| SALESMAN  |  5600 |
| TOTAL     | 32275 |
+-----------+-------+
6 rows in set, 1 warning (0.00 sec)

방법2)

mysql> select job, sum(sal) as sal
    -> from EMP
    -> group by job
    -> union all
    -> select 'TOTAL', sum(sal) from EMP;
+-----------+-------+
| job       | sal   |
+-----------+-------+
| ANALYST   |  6000 |
| CLERK     |  6850 |
| MANAGER   |  9825 |
| PRESIDENT |  4000 |
| SALESMAN  |  5600 |
| TOTAL     | 32275 |
+-----------+-------+
6 rows in set (0.00 sec)

== Oracle
Use the ROLLUP extension to GROUP BY to produce a grand total for all
salaries along with the subtotals for each JOB:

Use the GROUPING function in the JOB column to display a label for the
grand total. If the value of JOB is NULL, the GROUPING function will return 1, which signifies
that the value for SAL is the grand total created by ROLLUP. If the value of JOB is not NULL,
the GROUPING function will return 0, which signifies the value for SAL is the result of the
GROUP BY, not the ROLLUP.

select case grouping(job)
            when 0 then job
            else 'TOTAL'
        end job,
        sum(sal) sal
from emp
group by rollup(job);

[MySQL] Pivoting a Result Set into Multiple Rows

Leave a reply

= Pivoting a Result Set into Multiple Rows

mysql> select job, ename from EMP order by job;
+-----------+--------+
| job       | ename  |
+-----------+--------+
| ANALYST   | SCOTT  |
| ANALYST   | FORD   |
| CLERK     | JAMES  |
| CLERK     | ADAMS  |
| CLERK     | SMITH  |
| CLERK     | MILLER |
| MANAGER   | BLAKE  |
| MANAGER   | CLARK  |
| MANAGER   | JONES  |
| PRESIDENT | KING   |
| SALESMAN  | MARTIN |
| SALESMAN  | TURNER |
| SALESMAN  | WARD   |
| SALESMAN  | ALLEN  |
+-----------+--------+
14 rows in set (0.00 sec)

== Solution: MySQL

You would like to format the result set such that each job gets its own column:

 
mysql> select max(case when job ='clerk' then ename else null end) as clerks,
    ->   max(case when job ='analyst' then ename else null end) as analysts,
    ->   max(case when job ='manager' then ename else null end) as managers,
    ->   max(case when job ='president' then ename else null end) as presidents,
    ->   max(case when job ='salesman' then ename else null end) as salesmen
    -> from ( select e.job, e.ename,
    ->                (select count(*) from EMP d
    ->                   where e.job = d.job and e.empno < d.empno) as rnk
    ->         from EMP e
    ->       ) x
    -> group by rnk;
+--------+----------+----------+------------+----------+
| clerks | analysts | managers | presidents | salesmen |
+--------+----------+----------+------------+----------+
| MILLER | FORD     | CLARK    | KING       | TURNER   |
| JAMES  | SCOTT    | BLAKE    | NULL       | MARTIN   |
| ADAMS  | NULL     | JONES    | NULL       | WARD     |
| SMITH  | NULL     | NULL     | NULL       | ALLEN    |
+--------+----------+----------+------------+----------+
4 rows in set (0.00 sec)

mysql> select deptno, max(case when job ='clerk' then ename else null end) as clerks,
    ->   max(case when job ='analyst' then ename else null end) as analysts,
    ->   max(case when job ='manager' then ename else null end) as managers,
    ->   max(case when job ='president' then ename else null end) as presidents,
    ->   max(case when job ='salesman' then ename else null end) as salesmen
    -> from ( select e.deptno, e.job, e.ename,
    ->                (select count(*) from EMP d
    ->                   where e.job = d.job and e.empno < d.empno) as rnk
    ->         from EMP e
    ->       ) x
    -> group by deptno, rnk;
+--------+--------+----------+----------+------------+----------+
| deptno | clerks | analysts | managers | presidents | salesmen |
+--------+--------+----------+----------+------------+----------+
|     10 | MILLER | NULL     | CLARK    | KING       | NULL     |
|     20 | NULL   | FORD     | NULL     | NULL       | NULL     |
|     20 | NULL   | SCOTT    | NULL     | NULL       | NULL     |
|     20 | ADAMS  | NULL     | JONES    | NULL       | NULL     |
|     20 | SMITH  | NULL     | NULL     | NULL       | NULL     |
|     30 | NULL   | NULL     | NULL     | NULL       | TURNER   |
|     30 | JAMES  | NULL     | BLAKE    | NULL       | MARTIN   |
|     30 | NULL   | NULL     | NULL     | NULL       | WARD     |
|     30 | NULL   | NULL     | NULL     | NULL       | ALLEN    |
+--------+--------+----------+----------+------------+----------+
9 rows in set (0.00 sec)

== Solution: Oracle, SQL Server

select max(case when job ='clerk' then ename else null end) as clerks,
        max(case when job ='analyst' then ename else null end) as analysts,
        max(case when job ='manager' then ename else null end) as managers,
        max(case when job ='president' then ename else null end) as presidents,
        max(case when job ='salesman' then ename else null end) as salesmen
from ( select job, ename, 
                row_number() over(partition by job order by ename) rn
        from EMP 
      ) x
group by rn;

[MySQL] Pivoting a Result Set into One Row

Leave a reply

= Pivoting a Result Set into One Row

mysql> select deptno, count(*) from EMP group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
3 rows in set (0.02 sec)

== Solution 1
Transpose the result set using a CASE expression and the aggregate function SUM:

 
mysql> select sum(case when deptno = 10 then 1 else 0 end) as deptno_10,
    -> sum(case when deptno = 20 then 1 else 0 end) as deptno_20,
    -> sum(case when deptno = 30 then 1 else 0 end) as deptno_30
    -> from EMP;
+-----------+-----------+-----------+
| deptno_10 | deptno_20 | deptno_30 |
+-----------+-----------+-----------+
|         3 |         5 |         6 |
+-----------+-----------+-----------+
1 row in set (0.11 sec)

== Solution 2

mysql> select max(case when deptno = 10 then empcount else null end) as deptno_10,
    -> max(case when deptno = 20 then empcount else null end) as deptno_20,
    -> max(case when deptno = 30 then empcount else null end) as deptno_30
    -> from ( select deptno, count(*) as empcount 
    ->         from EMP group by deptno
    ->       ) x;
+-----------+-----------+-----------+
| deptno_10 | deptno_20 | deptno_30 |
+-----------+-----------+-----------+
|         3 |         5 |         6 |
+-----------+-----------+-----------+
1 row in set (0.00 sec)

[MySQL] the salary of the last employee hired in each department

Leave a reply

Finding Knight Values

You want return a result set that contains each employee’s name, the department they work
in, their salary, the date they were hired, and the salary of the last employee hired, in each
department. You want to return the following result set:

+--------+--------+------+------------+------------+
| deptno | ename  | sal  | hiredate   | latest_sal |
+--------+--------+------+------------+------------+
|     10 | MILLER | 4000 | 1982-01-23 |       4000 |
|     10 | KING   | 4000 | 1981-11-17 |       4000 |
|     10 | CLARK  | 4000 | 1981-06-09 |       4000 |
|     20 | ADAMS  | 1100 | 1983-01-12 |       1100 |
|     20 | SCOTT  | 3000 | 1982-12-09 |       1100 |
|     20 | FORD   | 3000 | 1981-12-03 |       1100 |
|     20 | JONES  | 2975 | 1981-04-02 |       1100 |
|     20 | SMITH  |  800 | 1980-12-17 |       1100 |
|     30 | JAMES  |  950 | 1981-12-03 |        950 |
|     30 | MARTIN | 1250 | 1981-09-28 |        950 |
|     30 | TURNER | 1500 | 1981-09-08 |        950 |
|     30 | BLAKE  | 2850 | 1981-05-01 |        950 |
|     30 | WARD   | 1250 | 1981-02-22 |        950 |
|     30 | ALLEN  | 1600 | 1981-02-20 |        950 |
+--------+--------+------+------------+------------+
14 rows in set (0.01 sec)


select e.deptno, e.ename, e.sal, e.hiredate,
  (select max(d.sal)
    from EMP d 
    where d.deptno = e.deptno
    		and d.hiredate = (select max(f.hiredate)
    			  from EMP f
    			  where f.deptno = e.deptno)
  ) as latest_sal
from EMP e
order by 1, 4 desc;

———————-
Oracle 9i and later

select deptno, ename, sal, hiredate,
max(sal)
keep(dense_rank last order by hiredate)
over(partition by deptno) latest_sal
from emp
order by 1, 4 desc

——————————
SQL Server, Oracle 8i

select deptno, ename, sal, hiredate,
         max(latest_sal)over(partition by deptno) latest_sal
from (
 select deptno, ename, sal, hiredate,
      case when hiredate = max(hiredate)over(partition by deptno)
             then sal else 0
      end latest_sal
from emp
) x
order by 1, 4 desc

[SQL] Filling in Missing Years in a Range of Dates

Leave a reply

Use table T10 as a pivot table (because it has 10 rows) and the built-in function EXTRACT to
generate one row for each year in the decade of 1980. Outer join to table EMP and count how
many employees were hired each year:

Result view:

YEAR CNT
---- ----------
1980 1
1981 10
1982 2
1983 1
1984 0
1985 0
1986 0
1987 0
1988 0
1989 0

== MySQL 

select y.yr, coalesce(x.cnt, 0) as cnt
from ( select min_year - mod(cast(min_year as decimal), 10) + rn as yr
       from ( select (select min(extract(year from hiredate)) from EMP) as min_year,
                 id - 1 as rn
               from T10
             ) a
      ) y
      left join
      ( select extract(year from hiredate) as yr, count(*) as cnt
         from EMP
         group by extract(year from hiredate)
      ) x
      on (y.yr = x.yr);

Result:

+------+------+
| yr   | cnt  |
+------+------+
| 1980 |    1 |
| 1981 |   10 |
| 1982 |    2 |
| 1983 |    1 |
| 1984 |    0 |
| 1985 |    0 |
| 1986 |    0 |
| 1987 |    0 |
| 1988 |    0 |
| 1989 |    0 |
+------+------+
10 rows in set (0.31 sec)

== T10 pivot table

mysql> select * from T10;
+------+
| ID   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.11 sec)

== EMP table

mysql> select empno,hiredate from EMP order by hiredate;
+-------+------------+
| empno | hiredate   |
+-------+------------+
|  7369 | 1980-12-17 |
|  7499 | 1981-02-20 |
|  7521 | 1981-02-22 |
|  7566 | 1981-04-02 |
|  7698 | 1981-05-01 |
|  7782 | 1981-06-09 |
|  7844 | 1981-09-08 |
|  7654 | 1981-09-28 |
|  7839 | 1981-11-17 |
|  7902 | 1981-12-03 |
|  7900 | 1981-12-03 |
|  7934 | 1982-01-23 |
|  7788 | 1982-12-09 |
|  7876 | 1983-01-12 |
+-------+------------+
14 rows in set (0.07 sec)

== Oracle

select x.yr, coalesce(cnt, 0) cnt
from ( select extract(year from min(hiredate) over()) - 
              mod(extract(year from min(hiredate) over()), 10) + rownum -1 yr
       from EMP
       where rownum <= 10
     ) x
     LEFT JOIN
     ( select to_number(to_char(hiredate, 'YYYY')) yr, count(*) cnt
       from EMP
       group by to_number(to_char(hiredate, 'YYYY'))
     ) y
     ON ( x.yr = y.yr)
order by x.yr;

[MySQL] Creating a calendar

1 Reply

참조: SQL Cookbook

select
max(case dw when 1 then dm end) as Su,
max(case dw when 2 then dm end) as Mo,
max(case dw when 3 then dm end) as Tu,
max(case dw when 4 then dm end) as We,
max(case dw when 5 then dm end) as Th,
max(case dw when 6 then dm end) as Fr,
max(case dw when 7 then dm end) as Sa
from (
select date_format(dy,’%U’) wk,
date_format(dy,’%d’) dm,
date_format(dy,’%w’)+1 dw
from (
select adddate(x.dy,T500.id-1) dy,
x.mth
from (
select adddate(current_date,-dayofmonth(current_date)+1) dy,
date_format(
adddate(current_date,
-dayofmonth(current_date)+1),
‘%m’) mth
from T1
) x,
T500
where T500.id <= 31
and date_format(adddate(x.dy,T500.id-1),’%m’) = x.mth
) y
) z
group by wk
order by wk;

mysql>
+------+------+------+------+------+------+------+
| Su   | Mo   | Tu   | We   | Th   | Fr   | Sa   |
+------+------+------+------+------+------+------+
| 01   | 02   | 03   | 04   | 05   | 06   | 07   |
| 08   | 09   | 10   | 11   | 12   | 13   | 14   |
| 15   | 16   | 17   | 18   | 19   | 20   | 21   |
| 22   | 23   | 24   | 25   | 26   | 27   | 28   |
| 29   | 30   | 31   | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+------+

mysql> select * from T1;
+------+
| ID   |
+------+
|    1 |
+------+


mysql> select * from T500;
+------+
| ID   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
.....
.....
|  495 |
|  496 |
|  497 |
|  498 |
|  499 |
|  500 |
+------+
500 rows in set (0.01 sec)

MySQL DATE_FORMAT() Function

Syntax:

DATE_FORMAT(date,format)

The formats that can be used are:

Format	Description
------ -----------------------------
%a	Abbreviated weekday name
%b	Abbreviated month name
%c	Month, numeric
%D	Day of month with English suffix
%d	Day of month, numeric (00-31)
%e	Day of month, numeric (0-31)
%f	Microseconds
%H	Hour (00-23)
%h	Hour (01-12)
%I	Hour (01-12)
%i	Minutes, numeric (00-59)
%j	Day of year (001-366)
%k	Hour (0-23)
%l	Hour (1-12)
%M	Month name
%m	Month, numeric (00-12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss AM or PM)
%S	Seconds (00-59)
%s	Seconds (00-59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00-53) where Sunday is the first day of week
%u	Week (00-53) where Monday is the first day of week
%V	Week (01-53) where Sunday is the first day of week, used with %X
%v	Week (01-53) where Monday is the first day of week, used with %x
%W	Weekday name
%w	Day of the week (0=Sunday, 6=Saturday)
%X	Year of the week where Sunday is the first day of week, four digits, used with %V
%x	Year of the week where Monday is the first day of week, four digits, used with %v
%Y	Year, four digits
%y	Year, two digits

[MySQL] 1~500 사이의 일련번호 생성 테이블

5 Replies

참조: http://stackoverflow.com/questions/186756/how-to-generate-a-range-of-numbers-in-mysql

table: T500
————–
id
————–
1
2
3

….
….
499
500
————–

1. 테이블 생성:
create table T500 (id int)
engine=MyISAM default charset=utf8;

2. 스토워드 프로시져(stored procedure):
delimiter ;;
create procedure dowhile()
begin
declare v1 int default 0;
while v1 <= 500 do
insert into T500 values( v1);
set v1 = v1 + 1;
end while;
end;;
delimiter ;

3. 스토워드 프로시져(sp) 호출:
call dowile();

select * from T500;

————————————
Oracle  stored procedure

declare
i number := 1;
begin
while i <= 500 loop
insert into T500 values(i);
i := i +1;
end loop;
end;

 

 

—————-

== Generate serial number in mysql query
http://stackoverflow.com/questions/11094466/generate-serial-number-in-mysql-query

serial_number | id
——————–
1 | 101
2 | 102
3 | 103
4 | 104
5 | 105

SELECT  @a:=@a+1 serial_number,id  FROM  T500,(SELECT @a:= 0) AS a;

[MySQL] Full Outer Join in MySQL

2 Replies

ref. http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql

Example Tables

Suppose we have the following tables:

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

FULL OUTER JOIN :

SELECT * FROM t1
LEFT OUTER JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT OUTER JOIN t2 ON t1.id = t2.id

The result would look like this:

1    Tim   1    Tim
2    Marta NULL NULL
NULL NULL  3    Katarina

[MySQL] root 패스워드 설정

11 Replies

참조:
Setting the root password on MySQL

[Mysql] password설정 및 변경, root 비번 잃어버렸을 경우 변경법

방법1)
# mysql -u root@localhost (Brings up the MySQL console)
#mysql> set password for root=password(‘password’); (Sets the root password to “password”)
#mysql> reload privileges; (Reloads the grant tables)

—————————-
방법2) update문으로 root사용자 패스워드 갱신

mysql> update user set password=password(’123′) where user = ‘root’;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> flush privileges;

[MySQL] Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

11 Replies

아래 나온 소스코드 http://localhost/testconnection.php 를 실행했을 때
Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
의 에러 메시지가 출력된 경우,
=>
/etc/my.cnf 파일 안에 socket 값을 다음처럼 설정하고

socket  = /var/run/mysqld/mysqld.sock

mysql 서버를 재실행하고
다시 http://localhost/testconnection.php 를 접속하면
mysql 서버 접속이 성공함

> sudo vim /var/www/testconnection.php

<?php
$link = mysql_connect('localhost', 'username', 'password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>

> sudo vim /etc/my.cnf

...
# The following options will be passed to all MySQL clients
[client]
#password       = your_password
#socket         = /tmp/mysql.sock
socket          = /var/run/mysqld/mysqld.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
#socket         = /tmp/mysql.sock
socket          = /var/run/mysqld/mysqld.sock
 ...