Integrate SQL Query into views I manage a Wordpress site for a football team, and I created therefore a SQL Query to calculate the standings.
Now I want to switch over to Drupal and for the calculation of the standings I thought to use the views module. Is it possible to integrate my SQL Query into Views?

Query : 

$sql="SELECT 
  teamname AS Team, Sum(Played) AS Played, Sum(Won) AS Won, Sum(Draw) AS Draw, Sum(Lost) AS Lost,
  SUM(Gfor) as Gfor, SUM(Gagainst) AS Gagainst, SUM(Gdiff) AS Gdiff, SUM(Pts) AS Pts


FROM(
  SELECT
    teamhome Team,
    1 Played,
    IF(homescore &gt; awayscore,1,0) Won,
    IF(homescore = awayscore,1,0) Draw,
    IF(homescore &lt; awayscore,1,0) Lost,
    homescore Gfor,
    awayscore Gagainst,
    homescore-awayscore Gdiff,
    CASE WHEN homescore &gt; awayscore THEN 3 WHEN homescore = awayscore THEN 1 ELSE 0 END Pts
  FROM matches
WHERE homescore IS NOT NULL AND awayscore IS NOT NULL
AND division='1' and season = '2015-2016' AND matchtype='C'
  UNION ALL
  SELECT
    teamaway,
    1,
    IF(homescore &lt; awayscore,1,0),
    IF(homescore = awayscore,1,0),
    IF(homescore &gt; awayscore,1,0),
    awayscore,
    homescore,
    awayscore-homescore Gdiff,
    CASE WHEN homescore &lt; awayscore THEN 3 WHEN homescore = awayscore THEN 1 ELSE 0 END
  FROM matches
  WHERE homescore IS NOT NULL AND awayscore IS NOT NULL
  AND division='1' and season = '2015-2016' AND matchtype='C'
) as tot

JOIN teams t ON tot.Team=t.teamid
GROUP BY Team

ORDER BY SUM(Pts) DESC, Sum(Won) DESC, SUM(Gdiff) DESC";
You will need to create a (1) custom table in the drupal database, (2) to adopt that table using the data module and (3) to create a view with that table as base the table of your view.

1) Create your custom table by using pure SQL (the fields order matters)

DROP TABLE IF EXISTS my_custom_table;
CREATE TABLE `my_custom_table` (
`node_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The primary identifier for a node.',
`int_field` int(10) unsigned DEFAULT NULL,
`text_field_1` varchar(255) NOT NULL DEFAULT,
`text_field_2` varchar(255) DEFAULT NULL,
`long_text_field` longtext,
`term_tid_field` int(10) unsigned NOT NULL DEFAULT '0',
`term_field` varchar(255) NOT NULL DEFAULT,
 PRIMARY KEY (`node_id`),
 KEY `text_field_1` (`text_field_1`),
 KEY `text_field_2` (`text_field_2`),
 KEY `long_text_field` (`long_text_field`),
 KEY `term_tid_field` (`term_tid_field`),
 KEY `term_field` (`term_field`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


You can peek at the drupal database for more field types

1.1) Continue by adding your data into that table by using SQL again:

INSERT IGNORE INTO my_custom_table(
SELECT
 int_field,
 text_field_1,
 text_field_2,
 long_text_field,
 term_tid_field,
 term_field
FROM
// Your query
);


2) Go to the data module's admin page admin/structure/data and adopt your custom table. The module is smart enough and your table will be displayed in the list, simple adopt it.

3) Create a new view admin/structure/views/add and if everything went well your table will be in the Show list with all other available tables. You should be able to see all the fields (1:1) from your table as fields in the view. 

A drawback (if it really is) by following that method is that no other fields are available, like node fields, taxonomy, users etc, just yours. The same goes for contextual filters handlers, filters criteria handlers and relationships. That's all.