You can try to use Aggregate function condition to make it.

Here is SQL-server sample:

CREATE TABLE My_Table(
  id INT,
  ItemCode VARCHAR(50),
  ItemAmount INT,
  Counter VARCHAR(50)
);


 INSERT INTO My_Table VALUES (1, '001', 1 ,'Counter-1');
 INSERT INTO My_Table VALUES (2, '001', 1 ,'Counter-2');
 INSERT INTO My_Table VALUES (3, '002', 2 ,'Counter-1');
 INSERT INTO My_Table VALUES (4, '002', 2 ,'Counter-2');
 INSERT INTO My_Table VALUES (5, '002', 1 ,'Counter-2');

Query 1:

select 
    ROW_NUMBER() OVER(ORDER BY itemCode) id,
    itemCode, 
    SUM(CASE WHEN Counter = 'Counter-1' THEN ItemAmount ELSE 0 END) as 'Count 1 Result',
    MAX(CASE WHEN Counter = 'COUNTER-1' THEN Counter END) as 'Count Is 1',
    SUM(CASE WHEN Counter = 'Counter-2' THEN ItemAmount ELSE 0 END) as 'Count 2 Result',
    MAX(CASE WHEN Counter = 'COUNTER-2' THEN Counter  END) as 'Count Is 2'
from 
    My_Table
GROUP BY 
    itemCode

Results:

| id | itemCode | Count 1 Result | Count Is 1 | Count 2 Result | Count Is 2 |
|----|----------|----------------|------------|----------------|------------|
|  1 |      001 |              1 |  Counter-1 |              1 |  Counter-2 |
|  2 |      002 |              2 |  Counter-1 |              3 |  Counter-2 |

Subquery in the WHERE Clause, It returns TRUE whenever the subquery returns one or more values. In its simplest Therefore, if your subquery returns a NULL value, the EXISTS statement resolves to TRUE. In the SalesOrderHeader WHERE SalesPersonID IN (279, 286, 289) whose results are result. So when I run the query it returns an empty table. Use single-row operators with single-row subqueries. If a subquery (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause. Type of Subqueries. Single row subquery : Returns zero or one row. Multiple row subquery : Returns one or more rows.

Try conditional aggregation, something like:

SELECT min(id) id,
       itemcode,
       sum(CASE
             WHEN counter = 'Counter-1' THEN
               itemamount
             ELSE
               0
           END) count1result,
       'Counter-1' countis1,
       sum(CASE
             WHEN counter = 'Counter-2' THEN
               itemamount
             ELSE
               0
           END) count2result,
       'Counter-2' countis2
       FROM my_table
       GROUP BY itemcode;

Chapter 31 – Searching with Subqueries, Queries return result sets, or Tables, and the values in such Tables can be (In fact, a one-Column Table subquery is such a common thing that some In this case, the UPDATE operation assigns a null value to COLUMN_1 in TABLE_1 . A subquery can appear anywhere an expression can be used, if it returns a single value. If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output (the select list of the outer query). Statements that include a subquery usually take one of these formats:

Even though I'm not sure what you are expecting with out disturbing your code I have given the query. Implement in your requirement

;WITH CTE AS (select 

    itemCode, 
    (select SUM(ItemAmount) where Counter = 'Counter-1') as 'Count 1 Result',
    (select MAX(Counter) where Counter = 'COUNTER-1') as 'Count Is 1',
    (select SUM(ItemAmount) where Counter = 'Counter-2') as 'Count 2 Result',
    (select MAX(Counter) where Counter = 'COUNTER-2') as 'Count Is 2'
from 
    My_table
    GROUP BY 
    itemCode,Counter )

    Select RANK()OVER ( ORDER BY itemcode)Id,
    itemCode,
    MAX([Count 1 Result])[Count 1 Result],
    MAX([Count Is 1])[Count Is 1],
    MAX([Count 2 Result])[Count 2 Result],
    MAX([Count Is 2])[Count Is 2] 
        from  CTE 
    GROUP BY itemCode

SQL Subqueries, The SQL subquery is a SELECT query that is embedded in the main SELECT statement. The main query (outer query) use the subquery result. We have the following two tables 'student' and 'marks' with common field 'StudentID'. If a subquery (inner query) returns a null value to the outer query, the  If the comparison gives a True value, a row is added to the result table. You can guarantee that a subquery will return a single value if you include an aggregate function in it. Aggregate functions always return a single value. Of course, this way of returning a single value is helpful only if you want the result of an aggregate function.

For the data retrieval part the following would give your data, by grouping on ItemCode and Counter:

select
    ItemCode,
    Counter,
    min(Id) as Id,
    sum(ItemAmount) as ItemAmount,
from 
    My_Table
group by ItemCode, Counter

ItemCode  Counter    Id   ItemAmount
========  =========  ===  ==========
001       Counter-1    1           1
001       Counter-2    1           2
002       Counter-1    3           1
002       Counter-2    3           3

To display that as so called pivot table, rows-to-columns, there are several solutions, one generic SQL solution:

In Modern SQL:

select ItemCode,
    ItemAmount filter (where Counter = 'Counter-1') as A1,
    ItemAmount filter (where Counter = 'Counter-2') as A2
from (select
        ItemCode,
        Counter,
        min(Id) as Id,
        sum(ItemAmount) as ItemAmount,
    from 
        My_Table
    group by ItemCode, Counter)
group by ItemCode

If filter is still not supported in the used SQL:

select ItemCode,
    sum(case when Counter = 'Counter-1' then ItemAmount end) as A1,
    sum(case when Counter = 'Counter-2' then ItemAmount end) as A2,

Test For Existence of Rows From a Subquery Using SQL EXISTS , In this tutorial, you will learn how to use SQL EXISTS to test if a subquery returns For each customer in the customers table, you check if there is at least one order If a subquery returns NULL , the expression EXIST NULL returns TRUE . A single row subquery returns zero or one row to the outer SQL statement. You can place a subquery in a WHERE clause, a HAVING clause, or a FROM clause of a SELECT statement. You can place a subquery in a WHERE clause, a HAVING clause, or a FROM clause of a SELECT statement.

The Power of Subqueries, Joining more than one virtual Table (SQL Server). The next example P.​Vorname, CASE R.Rat WHEN 1 THEN 'NR' WHEN 2 THEN 'SR' ELSE NULL END Rat, Here we used the first subquery to return a column value in the final result set. sql exists and null If the subquery returns NULL , the EXISTS operator still returns the result set. This is because the EXISTS operator only checks for the existence of row returned by the subquery.

Multiple Column Subqueries, So far you have written single-row subqueries and mulliple-row subqueries where only one column Logically, this SQL statement should have returned single rows. The reason is that all conditions that compare a null value result in a null. However, if the subquery can return a NULL, then NOT IN returns no rows at all. NULL really means ‘unknown’ rather than nothing, which is why any expression that compare to a NULL value returns NULL, or unknown. Logically, SQL Server evaluates the subquery, replaces it with the list of values it returns, and then evaluates the [NOT] IN

Consider using [NOT] EXISTS instead of [NOT] IN with a subquery , Imagine that we have two simple tables, one with all the common words In short, the SQL Server optimizer treats either query in the same way, and If you have a NULL value in the result of the subquery, or expression, that  Inner joins are mainly used to match the primary key of one table a foreign key in another. The second type of join is an outer join. Outer joins always return at least one row for the main table, referred to as the Left or Right table, and null values in the corresponding columns of the non-matching column. Outer joins are useful for finding

Comments

• add a tag of your dbms
• Use case expressions to do conditional aggregation.
• @hotfix can you give me an example of how to use tag please.
• @jarlh aren't case is when i want to set something conditionally ? but what i want to do is put the Counter 1 and Counter 2 side by side together. not choose only one by condition.
• @hakimhomecent you already have one tag SQL. edit you post and you can add more tags