Chemicals with no data

[sgosline]

There are chemicals in the portal without, it appears, associated data, e.g. https://superfund-portal.cgrb.oregonstate.edu/chemicals/view?id=10917

Do we need to include these?
@christinehc or @bartonmike can you get a sense of how many there are?

@sgosline , 1896 of the 2487 have nothing associated.

[bartonmike]

SELECT
vc.Chemical_ID AS chemical_id,
vc.PREFERRED_NAME AS chemical_name,
vc.cas_number AS cas_number,
vc.chemical_class AS chemical_class,

COALESCE(sc.sample_count, 0) AS sample_count,

/* ZF if zebrafish dose-response exists, else '' */
CASE
WHEN COALESCE(zf.zf_count, 0) > 0 THEN 'ZF'
ELSE ''
END AS zf_flag,

/* SG if sig gene stats exist, else '' */
CASE
WHEN COALESCE(sg.sg_count, 0) > 0 THEN 'SG'
ELSE ''
END AS sg_flag,

/* Optional combined flag column like 'ZF,SG'*/
CONCAT_WS(',',
CASE WHEN COALESCE(zf.zf_count, 0) > 0 THEN 'ZF' END,
CASE WHEN COALESCE(sg.sg_count, 0) > 0 THEN 'SG' END
) AS data_flags

FROM view_chemicals vc

LEFT JOIN (
SELECT
vstc.Chemical_ID,
COUNT(DISTINCT vstc.Sample_ID) AS sample_count
FROM view_samplesToChemicals vstc
GROUP BY vstc.Chemical_ID
) sc
ON sc.Chemical_ID = vc.Chemical_ID

LEFT JOIN (
SELECT
zcdr.Chemical_ID,
COUNT(zcdr.Dose) AS zf_count
FROM view_zebrafishChemDoseResponse zcdr
WHERE zcdr.Dose > 0
GROUP BY zcdr.Chemical_ID
) zf
ON zf.Chemical_ID = vc.Chemical_ID

LEFT JOIN (
SELECT
sgs.Chemical_ID,
COUNT(*) AS sg_count
FROM view_sigGeneStats sgs
GROUP BY sgs.Chemical_ID
) sg
ON sg.Chemical_ID = vc.Chemical_ID

where COALESCE(sc.sample_count, 0) = 0

and CONCAT_WS(',',
CASE WHEN COALESCE(zf.zf_count, 0) > 0 THEN 'ZF' END,
CASE WHEN COALESCE(sg.sg_count, 0) > 0 THEN 'SG' END
) = ''

ORDER BY sample_count DESC;

[bartonmike]

I limited it down to the 591 that we have data for. Im also attaching the lists of non-hits/hits/ and all

chemicals with data.xlsx
all chemicals.xlsx
chemicals without data.xlsx