Converting Access SQL to MySQL

Question

Hi I am trying to convert some Access SQL query over to a MySQL Query and have it a bump as i am unsure how to convert some of this code over. some one please help.

1.

SELECT Notices.Promoter, 
Sum(IIf([Notices].[Notice Type]='GRANT PERMIT' Or [Notices].[Notice Type]='GRANT VARIATION' Or [Notices].[Notice Type]='GRANT PAA',1,0)) AS Granted, 
Sum(IIf([Notices].[Notice Type]='REFUSE APPLICATION',1,0)) AS Refused, 
Sum(IIf([Notices].[Permit Status]='Deemed',1,0)) AS Deemed
FROM Notices
GROUP BY Notices.Promoter;

2.

SELECT [Notices].Promoter, 
Sum(IIf([Notices].[Notice Type]='VARIATION',1,0)) AS Variation, 
Sum(IIf([Notices].[Notice Type]='TWO HOURS AFTER',1,0)) AS [Two Hours After],
Sum(IIf([Notices].[Notice Type]='THREE MONTHS',1,0)) AS [Three Months],
Sum(IIf([Notices].[Notice Type]='THREE DAY',1,0)) AS [Three Day], 
Sum(IIf([Notices].[Notice Type]='TEN DAY',1,0)) AS [Ten Day]
FROM Notices
GROUP BY [Notices].Promoter;

The Access Database and MySQL database are labeled the same i just need to cover these two over so that i have the same result.

Lambda47 · Accepted Answer · 2013-03-26 05:10:26Z

2

access escape keyword use [], but mysql use ``, mysql can use CASE replace IIf.

SELECT `Notices`.Promoter, 
Sum(CASE WHEN `Notices`.`Notice Type`=`VARIATION` THEN 1 ELSE 0 END) AS Variation, 
Sum(CASE WHEN `Notices`.`Notice Type`='TWO HOURS AFTER' THEN 1 ELSE 0 END) AS `Two Hours    After`,
Sum(CASE WHEN `Notices`.`Notice Type`='THREE MONTHS' THEN 1 ELSE 0 END) AS `Three Months`,
Sum(CASE WHEN `Notices`.`Notice Type`='THREE DAY' THEN 1 ELSE 0 END) AS `Three Day`, 
Sum(CASE WHEN `Notices`.`Notice Type`='TEN DAY' THEN 1 ELSE 0 END) AS `Ten Day`
FROM Notices
GROUP BY `Notices`.Promoter;

answered Mar 26, 2013 at 5:10

Lambda47

362 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting Access SQL to MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related