Untitled query #11252

SQL

USE wikidatawiki_p;
SELECT page_title, pi_type, COUNT( tl_title ) AS c,
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Value_only%", "value",
       IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Qualifier" OR GROUP_CONCAT( tl_title ) LIKE "%Constraint:Qualifier&_%" ESCAPE "&", "qualifier",
         IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Source%", "source", ""))) "snak",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Single%", "x", "") "single",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Unique%", "x", "") "unique",
	IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Type%", "x", "") "type",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Item%", "x", "") "item",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Qualifiers%", "x", "") "qualifiers",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Format%", "x", "") "format",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:One_of%", "x", "") "one_of",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Value_type%", "x", "") "value type",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Target%", "x", "") "target",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Units%", "x", "") "units",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Diff%", "x", "") "diff"
FROM wb_property_info
JOIN page ON page_namespace = 121 AND page_title = CONCAT("P", pi_property_id)
LEFT JOIN templatelinks ON tl_title LIKE "Constraint:%" AND tl_namespace = 10 AND tl_from = page_id
GROUP BY page_title
ORDER BY c DESC, pi_property_id;
/*
SELECT * FROM wb_property_info WHERE pi_type IN ("string", "external-id") AND NOT EXISTS (
  SELECT * FROM templatelinks WHERE tl_from IN (
    SELECT page_id FROM page WHERE page_namespace = 121 AND page_title = CONCAT("P", pi_property_id)
  ) AND tl_namespace = 10 AND tl_title = "Constraint:Format"
);*/
SQL

Checking query status...
