SELECT * FROM (SELECT 
	a.id                                                                     AS "id",
	a.protocol                                                               AS "protocol",
	coalesce(a.compatibility_override, a.compatibility)                      AS "compliance",
	a.active                                                                 AS "active",
	a.baseurl                                                                AS "baseUrl",
	a.typology                                                               AS "type",
	coalesce(a.last_aggregation_date::text, a.last_download_date::text, '')  AS "aggrDate",
	coalesce(a.last_aggregation_total, a.last_download_total, 0)             AS "aggrTotal",
	ds.id                                                                    AS "dsId",
	ds.officialname                                                          AS "name",
	ds.englishname                                                           AS "alternativeName",
	ds.namespaceprefix                                                       AS "prefix",
	ds.websiteurl                                                            AS "websiteUrl",
	coalesce((array_agg(o.country))[1], '-')                                 AS "country",
    (array_agg(o.legalname))[1]                                              AS "organization",
    ds.consenttermsofuse                                                     AS "consenttermsofuse",
    ds.fulltextdownload                                                      AS "fulltextdownload"
FROM dsm_api a
	LEFT OUTER JOIN dsm_services ds ON (a.service = ds.id)
	LEFT OUTER JOIN dsm_service_organization dsorg ON (ds.id = dsorg.service)
	LEFT OUTER JOIN dsm_organizations o ON (dsorg.organization = o.id)
WHERE ds.merged_in IS NULL
GROUP BY
	a.id,
	a.protocol,
	a.compatibility_override,
	a.compatibility,
	a.active,
	a.last_aggregation_date,
	a.last_download_date,
	a.last_aggregation_total,
	a.last_download_total,
	ds.id,
	ds.officialname,
	ds.englishname,
	ds.namespaceprefix,
	ds.websiteurl,
	ds.consenttermsofuse,
    ds.fulltextdownload
) AS t WHERE
