CODEWITHSUNDEEP
phpmysqlms-access-2007
MasterT
MasterT

Reputation: 623

Converting Access SQL to MySQL

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.

Upvotes: 2

Views: 763

Answers (1)

Lambda47
Lambda47

Reputation: 36

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;

Upvotes: 2

Related Questions