SELECT  circ_lib."shortname" AS "Circ Library",
        acl."name" AS "Shelving Location",
        acn."label" AS "Call Number Label",
        rmsr."author" AS "Author",
        rmsr."title" AS "Title",
        mfr."tag" AS "MARC Tag",
        mfr."subfield" AS "MARC Subfield",
        mfr."value" AS "MARC Value",
        acp."barcode" AS "Barcode",
        COUNT(circ."id") AS "Circ ID"
  FROM  asset.copy AS acp
        INNER JOIN asset.call_number AS acn ON (acp."call_number" = acn."id")
        INNER JOIN biblio.record_entry AS bre ON (acn."record" = bre."id")
        LEFT OUTER JOIN reporter.materialized_simple_record AS rmsr ON (bre."id" = rmsr."id")
        LEFT OUTER JOIN metabib.full_rec AS mfr ON (bre."id" = mfr."record")
        INNER JOIN asset.copy_location AS acl ON (acp."location" = acl."id")
        INNER JOIN actor.org_unit AS circ_lib ON (acp."circ_lib" = circ_lib."id")
        LEFT OUTER JOIN action.circulation AS circ ON (acp."id" = circ."target_copy")
  WHERE ((mfr."tag") IS NULL OR mfr."tag" IN ('655'))
        AND acp."deleted" = 'f'
        AND ((mfr."subfield") IS NULL OR mfr."subfield" IN ('a'))
        AND ((mfr."value") IS NULL OR mfr."value" IN ('young adult fiction'))
        AND bre."id" > '0'
        AND circ_lib."id" IN ('18')
  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
  ORDER BY circ_lib."shortname" ASC, acl."name" ASC, acn."label" ASC, rmsr."author" ASC, rmsr."title" ASC, mfr."tag" ASC, mfr."subfield" ASC, mfr."value" ASC, acp."barcode" ASC, COUNT(circ."id") ASC