Validate counts in Oracle Sql

I have below a sample table where records can be plain (MULTI_ID IS NULL) or with a Master-Children related data (where MULTI_ID will repeat as many as Children in the XML qty element, plus the Master record). In the XML column of each row I have the number of expected Children a Master can have. I'm trying to validate that the numbers of Children in the table matches whatever is in the XML column. So far I have two separate queries that are showing me the results:

ID  MULTI_ID    XML
1   1           <data><qty>2</qty></data>
2   1           <data><qty>2</qty></data>
3   1           <data><qty>2</qty></data>
4   NULL        <data><qty>0</qty></data>
5   5           <data><qty>1</qty></data>
6   5           <data><qty>1</qty></data>
7   NULL        <data><qty>0</qty></data>
8   8           <data><qty>1</qty></data>

-- Getting number of children per master from XML

SELECT MULTI_ID, EXTRACT(XML, '/data/qty/text()').getStringVal() AS TOTAL FROM TABLE WHERE ID = MULTI_ID;

MULTI_ID    TOTAL
1           2
5           1
8           1

-- Getting total count of children from table

SELECT MULTI_ID, COUNT(*) - 1 AS TOTAL FROM TABLE WHERE MULTI_ID IN (
    SELECT MULTI_ID FROM TABLE WHERE ID = MULTI_ID
) GROUP BY MULTI_ID;

MULTI_ID    TOTAL
1           2
5           1
8           0

For above scenario, there's a discrepancy in the table for the records related to MULTI_ID = 8 where the Child is missing (it should be one record in the table for the Child, only the Master is present).

I want to have, if possible, just one query that will show only if there's any difference in the expected counts. In this specific scenario the query should show:

MULTI_ID    TOTAL   EXPECTED
8           0       1

Solution

You can use GROUP BY and HAVING:

SELECT COALESCE(multi_id, id) AS mutli_id,
       COUNT(*) - 1 AS expected,
       MIN(XMLCAST(XMLQUERY('/data/qty' PASSING XMLTYPE(xml) RETURNING CONTENT) AS NUMBER))
         AS actual
FROM   table_name
GROUP BY COALESCE(multi_id, id)
HAVING COUNT(*) - 1
       != MIN(XMLCAST(XMLQUERY('/data/qty' PASSING XMLTYPE(xml) RETURNING CONTENT) AS NUMBER))
AND    MIN(XMLCAST(XMLQUERY('/data/qty' PASSING XMLTYPE(xml) RETURNING CONTENT) AS NUMBER))
       = MAX(XMLCAST(XMLQUERY('/data/qty' PASSING XMLTYPE(xml) RETURNING CONTENT) AS NUMBER))

Which, for the sample data:

CREATE TABLE table_name (ID, MULTI_ID, XML) AS
SELECT 1, 1,    '<data><qty>2</qty></data>' FROM DUAL UNION ALL
SELECT 2, 1,    '<data><qty>2</qty></data>' FROM DUAL UNION ALL
SELECT 3, 1,    '<data><qty>2</qty></data>' FROM DUAL UNION ALL
SELECT 4, NULL, '<data><qty>0</qty></data>' FROM DUAL UNION ALL
SELECT 5, 5,    '<data><qty>1</qty></data>' FROM DUAL UNION ALL
SELECT 6, 5,    '<data><qty>1</qty></data>' FROM DUAL UNION ALL
SELECT 7, NULL, '<data><qty>0</qty></data>' FROM DUAL UNION ALL
SELECT 8, 8,    '<data><qty>1</qty></data>' FROM DUAL;

Outputs:

MUTLI_ID	EXPECTED	ACTUAL
8	0	1

fiddle