Space Calculation for SQL Server Table: What's wrong with my query? I'm using the following query in order to calculate some space related measurements on a particular table in my SQL Server database:
SELECT 
    t.name AS TableName, 
    p.rows,
    (sum(a.total_pages) * 8) as reserved,
    (sum(a.data_pages) * 8) as data,
    N'Not Needed' as index_size,
    (sum(a.total_pages) * 8) -  (sum(a.used_pages) * 8) as unused
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.object_id = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name = N'XYZ' 
GROUP BY t.name, p.rows
ORDER BY 
   1 Desc;

It gives me correct results for calculating number of rows, reserved space, and unused space. However, when I compare its output with the output from running sp_spaceused stored procedure, I observe a different value for space used by data:

How can I fix it?
The documentation for sys.allocation_units  is incorrect, it seems. It states that data_pages is number of used pages and it should include LOB pages.
LOB pages are pages for data types such as the (MAX) types, XML, geospatial and columnstore data.
But, looking on my machine (SQL server 2019), I see 0 data pages for LOB data. If you remove the GROUP BY and look at the individual rows, you'll see what I mean. So, you would have to CASE whether it is LOB data and use some other column in that case.
I've used the FactResellerSalesXL_CCI in the AdventureworksDW2016 database for my test. Adjust to your liking. I parameterized the schema name and table. See the discrepancy between the resulting column names data (your original calculation) and data_corrected (my adjusted calculation):
DECLARE @s sysname, @t sysname, @f nvarchar(256)
SET @s = 'dbo'
SET @t = 'FactResellerSalesXL_CCI'
SET @f = QUOTENAME(@s) + '.' + QUOTENAME(@t)

SELECT 
    t.name AS TableName, 
    a.type_desc,
    i.index_id,
    p.rows,
    a.total_pages * 8 as reserved,
    a.data_pages * 8 as data,
    (CASE WHEN a.type_desc = 'LOB_DATA' THEN a.used_pages ELSE a.data_pages END) * 8 as data_corrected,
    N'Not Needed' as index_size,
    a.total_pages * 8 -  a.used_pages * 8 as unused
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.object_id = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name = @t
ORDER BY 
   1 Desc;

I leave it to you to bring back the aggregation. :-)