CREATE TABLE ipaddress(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                       date VARCHAR(255),
                       ip VARCHAR(255));

INSERT INTO ipaddress(date, ip)
               VALUES('1-1-2012', '195.165.1.2'),
                     ('1-1-2012', '195.165.1.3'),
                     ('12-2-2012', '195.165.1.8');

Total number of Visits should be grouped based on IP Address

SELECT COUNT(ip), MONTHNAME(STR_TO_DATE(date, '%c-%e-%Y')) as period
  FROM ipaddress
 GROUP BY period;

Two tables

CREATE TABLE tblEatables (
    `EatId` int UNSIGNED PRIMARY AUTO_INCREMENT,
    `Fruits` varchar(9) NOT NULL
) Engine=InnoDB;

CREATE TABLE tblConfirm_Eatables (
    Eatables_Id INT UNSIGNED,
    Edible_Status INT,
    FOREIGN KEY Eatables_Id REFERENCES tblEatables (EatId)
) Engine=InnoDB;

Rows in Tables

INSERT INTO tblEatables(`EatId`, `Fruits`)
       VALUES(1, 'Apples'),
             (2, 'Oranges'),
             (3, 'Papaya'),
             (4, 'Jackfruit'),
             (5, 'Pineapple'),
             (6, 'Mango');

INSERT INTO tblConfirm_Eatables
    VALUES(1,0),
          (2,1),
          (3,0),
          (4,0);

The Result Should be

  Fruits
  Apple
  Papaya
  Jackfruit
  Pineapple
  Mango

Query1

  SELECT Fruits
    FROM tblEatables AS E LEFT JOIN 
         tblConfirm_Eatables AS CE ON E.EatID = CE.Eatables_ID
   WHERE CE.Edible_Status = 0 OR 
         CE.Edible_Status IS NULL

Query 2

 SELECT e.EatId,e.Fruits
   FROM @tblEatables e LEFT JOIN 
        @tblConfirm_Eatables ce ON e.EatId = ce.Eatbles_Id
  WHERE ce.Edible_Status  = 0 OR ce.Edible_Status IS Null

Query 3

 SELECT fruits 
   FROM tblEatables 
  WHERE EatID  NOT IN (SELECT Eatables_Id 
                         FROM tblConfirm_Eatables 
                        WHERE   Edible_Status = 1)           

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

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

How to View Active Connections & Process

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 > 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