SQL SUM and CASE and DISTINCT

I’ve been trying to re-do this statement but to no avail. SELECT b.Program AS Program, SUM(CASE WHEN a.Gender LIKE ‘M%’ THEN 1 ELSE NULL END) AS Males, SUM(CASE WHEN a.Gender LIKE ‘F%’ THEN 1 ELSE NULL END) AS Females, SUM(CASE WHEN e.Activity LIKE ‘Arts’ THEN 1 ELSE NULL END) AS Arts, AVG(CASE WHEN a.Gender LIKE … Read more

What’s wrong with the Foreign Key Constraint in this table?

MySQL 5.1.59 throws an error with this create table: CREATE TABLE IF NOT EXISTS `genre` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `abv` CHAR(3) CHARACTER SET ‘latin1’ COLLATE ‘latin1_bin’ NULL DEFAULT NULL , `name` VARCHAR(80) NOT NULL DEFAULT ” , `parent_id` INT NULL DEFAULT NULL , PRIMARY KEY (`id`) , INDEX `fk_genre_genre1` (`parent_id` ASC) , … Read more

Invalid cast when returning mysql LAST_INSERT_ID() using dapper.net

This question has been covered for MSSQL here: How do I perform an insert and return inserted identity with Dapper? but this solution does not work with mysql. To cast the LAST_INSERT_ID() to integer with mysql you have to do this: SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER); The stack trace is: Dapper.<QueryInternal>d__13`1.MoveNext() in sqlMapper.cs:607 System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) … Read more

How to check my data in SQL Server have carriage return and line feed? [duplicate]

This question already has answers here: SQL query for a carriage return in a string and ultimately removing carriage return (10 answers) Closed 7 years ago. Facing a problem, it seems my data stored in SQL Server does not stored correctly, simply put, how to verify that a varchar data has carriage return and line … Read more

MySQL now() change timezone

I’m using the following INSERT statement: INSERT INTO messages SET `to` = ‘”.$to.”‘, `from` = ‘”.$this->userid.”‘, `title` = ‘”.$title.”‘, `message` = ‘”.$message.”‘, `created` = NOW() However, it uses my server time (America/Montreal). I want time zone of Asia (Asia/Calcutta) Is this possible with the same query? Answer Better use the SQL format directly in your … Read more

python mysql delete statement not working

here I am trying to remove any users which containt a ” in their email/username. def removeQuote(self, tbl,record): “”” Updates the record “”” statmt=”select id from %s WHERE `email` LIKE ‘%%\”%%'” % (tbl) self.cursor.execute(statmt) rows=list(self.cursor.fetchall()) for idx, val in enumerate(rows): id= val[0] delstatmt = “DELETE FROM `maillist_subscription` WHERE id = ‘%s'” % id print delstatmt … Read more