{"id":32,"date":"2012-07-10T09:54:37","date_gmt":"2012-07-10T09:54:37","guid":{"rendered":"http:\/\/codeatelier.wordpress.com\/?p=32"},"modified":"2016-09-10T13:54:28","modified_gmt":"2016-09-10T13:54:28","slug":"mysql-queries","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/mysql-queries\/","title":{"rendered":"Mysql Queries"},"content":{"rendered":"

1.How To Create an SNo In MySql – Similar to row_num in MSSQL<\/strong><\/p>\n

\r\nSELECT @rownum:= @rownum+1 AS Sno\r\n  FROM Personal_details p,(SELECT @rownum:=0) r;\r\n<\/pre>\n
How to Subtract 7 Day from Now<\/strong><\/p>\n
\r\nSELECT DATE_SUB(NOW(),INTERVAL 7 DAY)<\/pre>\r\n<\/pre>\n
How to View Active Connections & Process<\/strong><\/p>\n
\r\nSHOW PROCESSLIST;\r\n<\/pre>\n
How to Get Balance<\/strong>
\nInput Table<\/strong>
\n\"\"<\/p>\n
Query 1<\/strong><\/p>\n
\r\n    SELECT date,\r\n          SUM(CASE\r\n               WHEN TYPE = 'cash_in' THEN amount\r\n               END) AS cash_in,\r\n          SUM(CASE\r\n                 WHEN TYPE = 'cash_out' THEN amount\r\n               END) AS cash_out,\r\n          SUM(CASE\r\n                WHEN TYPE = 'cash_in' THEN amount\r\n                ELSE -amount\r\n               END) AS balance\r\n    FROM tbSample4\r\n   WHERE type IN ('cash_in', 'cash_out') \r\n   GROUP BY date(date)\r\n<\/pre>\n
Query 2<\/strong><\/p>\n
\r\n SELECT a.currency_id, cash_in, cash_out, (cash_in - cash_out) balance\r\n   FROM (SELECT currency_id, SUM(AMOUNT) cash_in\r\n           FROM tbSample4\r\n          WHERE type = 'cash_in'\r\n          GROUP BY currency_id) a,\r\n        (SELECT currency_id, SUM(AMOUNT) cash_out\r\n           FROM tbSample4\r\n          WHERE type = 'cash_out'\r\n          GROUP BY currency_id) b\r\n  WHERE a.currency_id = b.currency_id\r\n<\/pre>\n
Output Table<\/strong>
\n\"\"<\/p>\n
2.How to Make Selected Names at the Top of List<\/strong><\/p>\n
\r\n SELECT Names\r\n   FROM tbNames<\/pre>\r\n\r\n<strong>Input Table<\/strong>\r\n<img src="http:\/\/codeatelier.files.wordpress.com\/2012\/07\/6.jpg" alt="" width="70" height="235" \/>\r\n\r\n<strong>Output<\/strong>\r\n\r\n<strong>Query1<\/strong>\r\n[code lang="sql"]\r\nSELECT Names\r\n  FROM tbNames\r\n ORDER BY CASE WHEN names IN ('Chandiran', 'Suryan') \r\n               THEN 0\r\n               ELSE 1\r\n               END<\/pre>\r\n<\/pre>\n
Query2<\/strong><\/p>\n
\r\n SELECT * \r\n   FROM tbNames\r\n  ORDER BY  Names IN ('Heman','superman') DESC;<\/pre>\r\n<img src="http:\/\/codeatelier.files.wordpress.com\/2012\/07\/7.jpg" alt="" width="75" height="241" \/>\r\n<\/pre>\n
3.How To Return Row in Column as Comma Separated Value CSV<\/strong><\/p>\n
 \r\n   SELECT GROUP_CONCAT(Name)\r\n     FROM tblNames\r\n    WHERE Id IN(15,16,17,18);\r\n<\/pre>\n
4.How Add OR Condition in Mysql case<\/strong><\/p>\n
\r\nCREATE TABLE tblConfirmationStatus (Confirm_Status TINYINT);\r\n\r\nINSERT INTO tblConfirmationStatus \r\nConfirm_Status\r\nVALUES\r\n(1),\r\n(0),\r\n(1),\r\n(null),\r\n(0),\r\n(1),\r\n(null);\r\n<\/pre>\n
Required Output<\/strong>
\nConfirmStatus<\/p>\n
   Confirmed\r\n   Not Confirmed\r\n   Confirmed\r\n   Not Confirmed\r\n   Not Confirmed\r\n   Confirmed\r\n   Not Confirmed\r\n<\/pre>\n
Query 1<\/strong><\/p>\n
\r\n SELECT CASE \r\n        WHEN Confirm_Status IS NULL OR Confirm_Status = 0 THEN 'Not Confirmed' \r\n        ELSE  'Confirmed' \r\n       END AS ConfirmStatus\r\n  FROM tblConfirmationStatus;\r\n<\/pre>\n
Query 2<\/strong><\/p>\n
\r\n SELECT CASE \r\n        WHEN Confirm_Status &gt; 0 THEN 'Confirmed' \r\n        ELSE  'Not Confirmed' \r\n       END AS ConfirmStatus\r\n  FROM tblConfirmationStatus;\r\n<\/pre>\n
MySQL Query To Skip Days – Sunday as Below<\/strong><\/p>\n
\r\nCREATE TABLE Patients_Appointment_List(Patent_Id INT  UNSIGNED PRIMARY KEY AUTO_INCREMENT,Patient_Name VARCHAR(255),appointment_Date DATE);\r\n\r\nINSERT INTO Patients_Appointment_List\r\n (Patient_Name, appointment_Date)\r\n  VALUES\r\n ('Mugil', 20120730),\r\n ('Vinu',  20120730),\r\n ('Madhavan',  20120729),\r\n ('Gopal',  20120730),\r\n ('Shivaji',  20120731);\r\n\r\nSELECT Patient_Name\r\n  FROM Patients_Appointment_List\r\n WHERE CASE WHEN WEEKDAY(CURDATE() + INTERVAL 1 DAY) = 6 THEN \r\n       appointment_Date BETWEEN NOW() AND NOW() + INTERVAL 2 DAY \r\n            ELSE appointment_Date BETWEEN NOW() AND NOW() + INTERVAL 1 DAY\r\n       END\r\n<\/pre>\n
Output as on 20120728<\/strong>
\nPatient_Name<\/strong><\/p>\n
\r\nMugil\r\nVinu\r\nMadhavan\r\nGopal\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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… Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,20],"tags":[],"class_list":["post-32","post","type-post","status-publish","format-standard","hentry","category-mysql","category-queries"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/32","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=32"}],"version-history":[{"count":2,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/32\/revisions"}],"predecessor-version":[{"id":1578,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/32\/revisions\/1578"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=32"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=32"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=32"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}