CREATE VIEW vwlicensecountsbytype AS (
 SELECT l.licensename,
    l.licensetype,
    count(DISTINCT l.userid) AS totalusers,
    sum(
        CASE
            WHEN (l.licenseactive = true) THEN 1
            ELSE 0
        END) AS activelicenses,
    sum(
        CASE
            WHEN (l.licenseactive = false) THEN 1
            ELSE 0
        END) AS inactivelicenses,
    sum(
        CASE
            WHEN (l.copilotlicense = true) THEN 1
            ELSE 0
        END) AS copilotlicenses,
    min(l.licenseassignmentdate) AS earliestassignment,
    max(l.licenseassignmentdate) AS latestassignment,
    max(l.updated) AS lastupdated
   FROM subuserlicensedata l
  WHERE (l.licensename IS NOT NULL)
  GROUP BY l.licensename, l.licensetype
)