Mysql Queries « CodeThatAint

1.How To Create an SNo In MySql – Similar to row_num in MSSQL

SELECT @rownum:= @rownum+1 AS Sno
  FROM Personal_details p,(SELECT @rownum:=0) r;

How to Subtract 7 Day from Now

1	`SELECT` `DATE_SUB(NOW(),INTERVAL 7` `DAY)</pre>`

How to View Active Connections & Process

1	`SHOW PROCESSLIST;`

How to Get Balance
Input Table

Query 1

SELECT date,
       SUM(CASE
            WHEN TYPE = 'cash_in' THEN amount
            END) AS cash_in,
       SUM(CASE
              WHEN TYPE = 'cash_out' THEN amount
            END) AS cash_out,
       SUM(CASE
             WHEN TYPE = 'cash_in' THEN amount
             ELSE -amount
            END) AS balance
 FROM tbSample4
WHERE type IN ('cash_in', 'cash_out') 
GROUP BY date(date)

Query 2

SELECT a.currency_id, cash_in, cash_out, (cash_in - cash_out) balance
  FROM (SELECT currency_id, SUM(AMOUNT) cash_in
          FROM tbSample4
         WHERE type = 'cash_in'
         GROUP BY currency_id) a,
       (SELECT currency_id, SUM(AMOUNT) cash_out
          FROM tbSample4
         WHERE type = 'cash_out'
         GROUP BY currency_id) b
 WHERE a.currency_id = b.currency_id

Output Table

2.How to Make Selected Names at the Top of List

SELECT Names
   FROM tbNames</pre>
 
<strong>Input Table</strong>
<img src="http://codeatelier.files.wordpress.com/2012/07/6.jpg" alt="" width="70" height="235" />
 
<strong>Output</strong>
 
<strong>Query1</strong>
[code lang="sql"]
SELECT Names
  FROM tbNames
 ORDER BY CASE WHEN names IN ('Chandiran', 'Suryan') 
               THEN 0
               ELSE 1
               END</pre>

Query2

SELECT * 
   FROM tbNames
  ORDER BY  Names IN ('Heman','superman') DESC;</pre>
<img src="http://codeatelier.files.wordpress.com/2012/07/7.jpg" alt="" width="75" height="241" />

3.How To Return Row in Column as Comma Separated Value CSV

SELECT GROUP_CONCAT(Name)
  FROM tblNames
 WHERE Id IN(15,16,17,18);

4.How Add OR Condition in Mysql case

CREATE TABLE tblConfirmationStatus (Confirm_Status TINYINT);
 
INSERT INTO tblConfirmationStatus 
Confirm_Status
VALUES
(1),
(0),
(1),
(null),
(0),
(1),
(null);

Required Output
ConfirmStatus

   Confirmed
   Not Confirmed
   Confirmed
   Not Confirmed
   Not Confirmed
   Confirmed
   Not Confirmed

Query 1

SELECT CASE
       WHEN Confirm_Status IS NULL OR Confirm_Status = 0 THEN 'Not Confirmed'
       ELSE  'Confirmed'
      END AS ConfirmStatus
 FROM tblConfirmationStatus;

Query 2

SELECT CASE
       WHEN Confirm_Status &gt; 0 THEN 'Confirmed'
       ELSE  'Not Confirmed'
      END AS ConfirmStatus
 FROM tblConfirmationStatus;

MySQL Query To Skip Days – Sunday as Below

CREATE TABLE Patients_Appointment_List(Patent_Id INT  UNSIGNED PRIMARY KEY AUTO_INCREMENT,Patient_Name VARCHAR(255),appointment_Date DATE);
 
INSERT INTO Patients_Appointment_List
 (Patient_Name, appointment_Date)
  VALUES
 ('Mugil', 20120730),
 ('Vinu',  20120730),
 ('Madhavan',  20120729),
 ('Gopal',  20120730),
 ('Shivaji',  20120731);
 
SELECT Patient_Name
  FROM Patients_Appointment_List
 WHERE CASE WHEN WEEKDAY(CURDATE() + INTERVAL 1 DAY) = 6 THEN
       appointment_Date BETWEEN NOW() AND NOW() + INTERVAL 2 DAY
            ELSE appointment_Date BETWEEN NOW() AND NOW() + INTERVAL 1 DAY
       END

Output as on 20120728
Patient_Name

Mugil
Vinu
Madhavan
Gopal

Leave a reply Cancel reply