-- SELECT ROUND(AVG(compressionRatio),5)
-- FROM DiffPrevFrameBenchmark
-- WHERE diffType = 'PrevFrameDiff_BSDIFF' AND filename = '16Bit-ZStack.czi' AND level = 6 AND compression = 'BZIP2';

-- SELECT * -- AVG(compressionRatio) as [cr]
-- FROM DiffPrevFrameBenchmark
-- WHERE filename = 'AxioZoom_Artemia_AT-1Ch-Z_sect.czi' and diffType = 'PrevFrameDiff_BSDIFF' and compression = 'BZIP2' AND level = 6

SELECT * FROM
(
    SELECT ROUND((fbc.cr - referenceCompression.cr),5) as [artemia_div] FROM
    (
        SELECT AVG(compressionRatio) as [cr], compressedSize
        FROM DiffPrevFrameBenchmark
        WHERE filename = 'AxioZoom_Artemia_AT-1Ch-Z_sect.czi' and diffType = 'PrevFrameDiff_BSDIFF' and compression = 'BZIP2' AND level = 6
    ) fbc,
    (
        SELECT AVG(compressionRatio) as [cr], compressedSize
        FROM CompressionBenchmark
        WHERE filename = 'AxioZoom_Artemia_AT-1Ch-Z_sect.czi' and compression = 'BZIP2' AND level = 6
    ) referenceCompression
) artemia_diff,
(
    SELECT ROUND((fbc.cr - referenceCompression.cr),5) as [artemia_flash_diff] FROM
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = '40x075_Artemia-Flash-AT-1Ch-Z-sect.czi' and diffType = 'PrevFrameDiff_BSDIFF' and compression = 'BZIP2' AND level = 6
    ) fbc,
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM CompressionBenchmark
        WHERE filename = '40x075_Artemia-Flash-AT-1Ch-Z-sect.czi' and compression = 'BZIP2' AND level = 6
    ) referenceCompression
) artemia_flash_diff,
(
    SELECT ROUND((fbc.cr - referenceCompression.cr),5) as [llc_diff] FROM
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = 'LLC-PK1_TubX-emerald_H2B-mCherry-2chZ(SD).czi' and diffType = 'PrevFrameDiff_BSDIFF' and compression = 'BZIP2' AND level = 6
    ) fbc,
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM CompressionBenchmark
        WHERE filename = 'LLC-PK1_TubX-emerald_H2B-mCherry-2chZ(SD).czi'  and compression = 'BZIP2' AND level = 6
    ) referenceCompression
) llc_diff,
(
    SELECT ROUND((fbc.cr - referenceCompression.cr),5) as [bit16_diff] FROM
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = '16Bit-ZStack.czi' and diffType = 'PrevFrameDiff_BSDIFF' and compression = 'BZIP2' AND level = 6
    ) fbc,
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM CompressionBenchmark
        WHERE filename = '16Bit-ZStack.czi' and compression = 'BZIP2' AND level = 6
    ) referenceCompression
) bit16_diff;

/*
-- FIXED BIT COUNT
SELECT * FROM
(
    SELECT ROUND((fbc.cr - referenceCompression.cr),5) as [artemia_div] FROM
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = 'AxioZoom_Artemia_AT-1Ch-Z_sect.czi' and diffType = 'PrevFrameDiff_BSDIFF' and compression = 'BZIP2' AND level = 6
    ) fbc,
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM CompressionBenchmark
        WHERE filename = 'AxioZoom_Artemia_AT-1Ch-Z_sect.czi' and compression = 'BZIP2' AND level = 6
    ) referenceCompression
) artemia_diff,
(
    SELECT ROUND((fbc.cr - referenceCompression.cr),5) as [artemia_flash_diff] FROM
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = '40x075_Artemia-Flash-AT-1Ch-Z-sect.czi' and diffType = 'PrevFrameDiff_BSDIFF' and compression = 'BZIP2' AND level = 6
    ) fbc,
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM CompressionBenchmark
        WHERE filename = '40x075_Artemia-Flash-AT-1Ch-Z-sect.czi' and compression = 'BZIP2' AND level = 6
    ) referenceCompression
) artemia_flash_diff,
(
    SELECT ROUND((fbc.cr - referenceCompression.cr),5) as [llc_diff] FROM
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = 'LLC-PK1_TubX-emerald_H2B-mCherry-2chZ(SD).czi' and diffType = 'PrevFrameDiff_BSDIFF' and compression = 'BZIP2' AND level = 6
    ) fbc,
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM CompressionBenchmark
        WHERE filename = 'LLC-PK1_TubX-emerald_H2B-mCherry-2chZ(SD).czi'  and compression = 'BZIP2' AND level = 6
    ) referenceCompression
) llc_diff,
(
    SELECT ROUND((fbc.cr - referenceCompression.cr),5) as [bit16_diff] FROM
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM DiffPrevFrameBenchmark
        WHERE filename = '16Bit-ZStack.czi' and diffType = 'PrevFrameDiff_BSDIFF' and compression = 'BZIP2' AND level = 6
    ) fbc,
    (
        SELECT AVG(compressionRatio) as [cr]
        FROM CompressionBenchmark
        WHERE filename = '16Bit-ZStack.czi' and compression = 'BZIP2' AND level = 6
    ) referenceCompression
) bit16_diff;
*/