NoPublic, bureaucrat, administrator
1,068
edits
Changes
no edit summary
CREATE VIEW `my_view` AS
SELECT
sum(if((`nominefound_view_devicemy_view2`.`Device Type` = _utf8'SAA'),`nominefound_view_devicemy_view2`.`Quantity`,0)) AS`SAA`,sum(if((`nominefound_view_devicemy_view2`.`Device Type` = _utf8'AP'),`nominefound_view_devicemy_view2`.`Quantity`,0)) AS `AP`,sum(if((`nominefound_view_devicemy_view2`.`Device Type` = _utf8'AIED'),`nominefound_view_devicemy_view2`.`Quantity`,0)) AS `AIED`,sum(if((`nominefound_view_devicemy_view2`.`Device Type` = _utf8'UXO'),`nominefound_view_devicemy_view2`.`Quantity`,0)) AS `UXO`,sum(if((`nominefound_view_devicemy_view2`.`Device Type` = _utf8'AT'),`nominefound_view_devicemy_view2`.`Quantity`,0)) AS `AT`,sum(if((`nominefound_view_devicemy_view2`.`Device Type` = _utf8'Fragments'),`nominefound_view_devicemy_view2`.`Quantity`,0)) AS `Fragments`
FROM
`my_view2`;
CREATE VIEW my_view AS
SELECT
sum(case when nominefound_view_devicemy_view2.Device Type = 'SAA') then nominefound_view_devicemy_view2.Quantity else 0 end) AS "SAA",sum(case when nominefound_view_devicemy_view2.Device Type = 'AP') then nominefound_view_devicemy_view2.Quantity else 0 end) AS "AP",sum(case when nominefound_view_devicemy_view2.Device Type = 'AIED') then nominefound_view_devicemy_view2.Quantity else 0 end) AS "AIED",sum(case when nominefound_view_devicemy_view2.Device Type = 'UXO') then nominefound_view_devicemy_view2.Quantity else 0 end) AS "UXO",sum(case when nominefound_view_devicemy_view2.Device Type = 'AT') then nominefound_view_devicemy_view2.Quantity else 0 end) AS "AT",sum(case when nominefound_view_devicemy_view2.Device Type = 'Fragments') then nominefound_view_devicemy_view2.Quantity else 0 end) AS "Fragments"
FROM
my_view2;
END
</pre>
=== Date manipulations ===__NOEDITSECTION__
There are differences between MySQL and PostgreSQL when it comes to manipulating fields of type DATE, such as for example extracting the year or the month of a date. In MySQL, the related functions are year() and month(). In PostgreSQL, there is an extract() function that does the job. See the below example for reference. The following create view statement:<br />
'''MySQL'''
<pre>
CREATE VIEW `my_view` AS
SELECT
(year(`my_view2`.`DateofAccident`) - year(`my_view2`.`DateofBirth`)) AS `Age`,
year(`my_view2`.`DateofAccident`) AS `Year`,
month(`my_view2`.`DateofAccident`) AS `Month`
FROM `my_view2`;
</pre>
needs to be changed to:<br />
'''PostgreSQL'''
<pre>
CREATE VIEW `my_view` AS
SELECT
extract(year from my_view2."DateofAcciden") - extract(year from my_view2."DateofBirth") AS "Age",
extract(year from my_view2."DateofAccident") AS "Year",
extract(month from my_view2."DateofAccident") AS "Month"
FROM my_view2;
</pre>
=== The GROUP BY clause ===__NOEDITSECTION__
