SQL: help add secondary filter to chart query

Need query that returns (x, y1, y2) for JFreeChart JDBC via HSQLDB. This is what I have so far: SELECT "X", "Y" AS "**Y1**" FROM TABLE WHERE A=1,B=11,in1='chocolate',(ABS(in2 - 1000.0) < 0.01) ORDER BY "ID"; How do I filter Y differently for Y2? My threads are writing the data to the table so I could write *multiple *tables like TABLEi-j if that makes this easier? My google and stack overflow searches cite examples of nested sub queries and common table expression (CTE) but I have yet to grasp this advanced concept.

I made some sample data in Excel with VBA macro. The real Java program is using ID as index to List when the Callable does the work writing to the in-process in-memory database for the graph which dynamically updates with a timer firing off this query.

ID  A   B   in1         in2         X       Y
1   1   11  chocolate   1,000.00    1.00    41.01
2   1   11  chocolate   1,000.00    1.50    41.28
3   1   11  chocolate   1,000.00    2.00    71.27
4   1   12  chocolate   1,000.00    1.00    32.62
5   1   12  chocolate   1,000.00    1.50    63.32
6   1   12  chocolate   1,000.00    2.00    20.76
7   2   11  chocolate   1,000.00    1.00    18.60
8   2   11  chocolate   1,000.00    1.50    58.34
9   2   11  chocolate   1,000.00    2.00    8.07
10  2   12  chocolate   1,000.00    1.00    45.80
11  2   12  chocolate   1,000.00    1.50    90.57
12  2   12  chocolate   1,000.00    2.00    26.14

Sub Macro1()
   Debug.Print ("ID,A,B,in1,in2,X,Y") ' CSV table header record
   counter% = 0
   For i% = 1 To 2
      For j% = 11 To 12
         For k = 1# To 2# Step 0.5
            counter% = counter% + 1
            Debug.Print (counter% & "," & i% & "," & j% & ",chocolate,1000.0," & k & "," & Rnd * 100#)
         Next k
      Next j%
    Next i%
End Sub

I tried nested sub queries, CTE, and separate tables using JOIN but my SQL skills are not advanced.

Below I use 2 different aggregation functions MAX() and SUM() however it appears you may want a "counter" so instead use COUNT().

I don't believe you need CTE's or sub-queries (although I'm not sure if I really understand your question in full). So I'm presenting 3 queries. The first just demonstrates what a case expression can achieve this will give you Y1 and Y2 but probably this isn't the style of output you want to graph. The second query shows how you may combine the use of case expression inside aggregate function (here I use MAX()) and the GROUP BY clause which summarises the data by the columns listed in that clause. Finally, there is a seemingly more complex query, but this display how you could filter Y1 and Y2 independently (and you would then also adjust the case expressions to match). Again this query uses a GROUP BY clause, but here it uses SUM() as the aggregate function.

CREATE TABLE YourTable (
    ID INT PRIMARY KEY,
    A INT,
    B INT,
    in1 VARCHAR(255),
    in2 DECIMAL(10, 2),
    X DECIMAL(10, 2),
    Y DECIMAL(10, 2)
);

INSERT INTO YourTable (ID, A, B, in1, in2, X, Y) VALUES
(1, 1, 11, 'chocolate', 1000.00, 1.00, 41.01),
(2, 1, 11, 'chocolate', 1000.00, 1.50, 41.28),
(3, 1, 11, 'chocolate', 1000.00, 2.00, 71.27),
(4, 1, 12, 'chocolate', 1000.00, 1.00, 32.62),
(5, 1, 12, 'chocolate', 1000.00, 1.50, 63.32),
(6, 1, 12, 'chocolate', 1000.00, 2.00, 20.76),
(7, 2, 11, 'chocolate', 1000.00, 1.00, 18.60),
(8, 2, 11, 'chocolate', 1000.00, 1.50, 58.34),
(9, 2, 11, 'chocolate', 1000.00, 2.00, 8.07),
(10, 2, 12, 'chocolate', 1000.00, 1.00, 45.80),
(11, 2, 12, 'chocolate', 1000.00, 1.50, 90.57),
(12, 2, 12, 'chocolate', 1000.00, 2.00, 26.14);

1. Simple select query with case expressions

SELECT
      X
    , CASE WHEN A = 1 AND B = 11 THEN Y END AS Y1
    , CASE WHEN A = 2 AND B = 12 THEN Y END AS Y2
FROM YourTable
WHERE in1 = 'chocolate'
    AND ABS(in2 - 1000.0) < 0.01
ORDER BY ID

2. Grouping query with "conditional aggregation" (case expression inside aggregate function)

SELECT
      X
    , MAX(CASE WHEN A = 1 AND B = 11 THEN Y END) AS Y1
    , MAX(CASE WHEN A = 2 AND B = 12 THEN Y END) AS Y2
FROM YourTable
WHERE in1 = 'chocolate'
    AND ABS(in2 - 1000.0) < 0.01
GROUP BY
      X
ORDER BY
      X

3. Query that allows for "independent filtering" of Y1 and Y2, also with conditional aggregation that match the filters.

SELECT
      X
    , SUM(CASE WHEN A = 1
                AND B = 11
                AND in1 = 'chocolate'
                AND ABS(in2 - 1000.0) < 0.01 THEN Y ELSE 0 END) AS Y1
    , SUM(CASE WHEN A = 2
                AND B = 12
                AND in1 = 'chocolate'
                AND ABS(in2 - 1000.0) < 0.01 THEN Y ELSE 0 END) AS Y2
FROM YourTable
WHERE (
        A = 1
        AND B = 11
        AND in1 = 'chocolate'
        AND ABS(in2 - 1000.0) < 0.01
        )
    OR (
        A = 2
        AND B = 12
        AND in1 = 'chocolate'
        AND ABS(in2 - 1000.0) < 0.01
        )
GROUP BY
      X
ORDER BY
      X

fiddle