result in value get proper t but while it put in row it not give result as per value

[dhrupal_shah]

Dear all,

refer below screen shots and query,

i want result as 3rd screen if i put it in row or in value. 

thanks,

Dhrupal Shah

SELECT *

  ,W1=CASE WHEN Item_Width BETWEEN 0 AND 100 THEN 'W_0100' ELSE '' END

  ,W2=CASE WHEN Item_Width BETWEEN 100.01 AND 300 THEN 'W_100300' ELSE '' END

  ,W3=CASE WHEN Item_Width BETWEEN 300.01 AND 600 THEN 'W_300600' ELSE '' END

  ,W4=CASE WHEN Item_Width > 600.01 THEN 'W_abv600' ELSE '' END

  ,L1=CASE WHEN Item_Length BETWEEN 0 AND 300 THEN 'W_0300' ELSE '' END

  ,L2=CASE WHEN Item_Length BETWEEN 300.01 AND 600 THEN 'W_300600' ELSE '' END

  ,L3=CASE WHEN Item_Length BETWEEN 600.01 AND 900 THEN 'W_600900' ELSE '' END

  ,L4=CASE WHEN Item_Length >900.01 THEN 'W_abv900' ELSE '' END

  ,W1_0_100=CASE WHEN Item_Width BETWEEN 0 AND 100 THEN QtyRecv ELSE 0 END

  ,W2_100_300=CASE WHEN Item_Width BETWEEN 100.01 AND 300 THEN QtyRecv ELSE 0 END

  ,W3_300_600=CASE WHEN Item_Width BETWEEN 300.01 AND 600 THEN QtyRecv ELSE 0 END

  ,W4_abv_600=CASE WHEN Item_Width > 600.01 THEN QtyRecv ELSE 0 END

  ,L1_0_300=CASE WHEN Item_Length BETWEEN 0 AND 300 THEN QtyRecv ELSE 0 END

  ,L2_300_600=CASE WHEN Item_Length BETWEEN 300.01 AND 600 THEN QtyRecv ELSE 0 END

  ,L3_600_900=CASE WHEN Item_Length BETWEEN 600.01 AND 900 THEN QtyRecv ELSE 0 END

  ,L4_abv_900=CASE WHEN Item_Length >900.01 THEN QtyRecv ELSE 0 END

[saud968]

If you want to sum quantities based on these categories, you might need to use aggregation functions like SUM() in your query.

Here’s a revised version of your query with some adjustments:

SQL

SELECT
CASE
WHEN Item_Width BETWEEN 0 AND 100 THEN 'W_0100'
WHEN Item_Width BETWEEN 100.01 AND 300 THEN 'W_100300'
WHEN Item_Width BETWEEN 300.01 AND 600 THEN 'W_300600'
WHEN Item_Width > 600.01 THEN 'W_abv600'
ELSE ''
END AS Width_Category,
CASE
WHEN Item_Length BETWEEN 0 AND 300 THEN 'L_0300'
WHEN Item_Length BETWEEN 300.01 AND 600 THEN 'L_300600'
WHEN Item_Length BETWEEN 600.01 AND 900 THEN 'L_600900'
WHEN Item_Length > 900.01 THEN 'L_abv900'
ELSE ''
END AS Length_Category,
SUM(CASE WHEN Item_Width BETWEEN 0 AND 100 THEN QtyRecv ELSE 0 END) AS W1_0_100,
SUM(CASE WHEN Item_Width BETWEEN 100.01 AND 300 THEN QtyRecv ELSE 0 END) AS W2_100_300,
SUM(CASE WHEN Item_Width BETWEEN 300.01 AND 600 THEN QtyRecv ELSE 0 END) AS W3_300_600,
SUM(CASE WHEN Item_Width > 600.01 THEN QtyRecv ELSE 0 END) AS W4_abv_600,
SUM(CASE WHEN Item_Length BETWEEN 0 AND 300 THEN QtyRecv ELSE 0 END) AS L1_0_300,
SUM(CASE WHEN Item_Length BETWEEN 300.01 AND 600 THEN QtyRecv ELSE 0 END) AS L2_300_600,
SUM(CASE WHEN Item_Length BETWEEN 600.01 AND 900 THEN QtyRecv ELSE 0 END) AS L3_600_900,
SUM(CASE WHEN Item_Length > 900.01 THEN QtyRecv ELSE 0 END) AS L4_abv_900
FROM YourTable
GROUP BY
CASE
WHEN Item_Width BETWEEN 0 AND 100 THEN 'W_0100'
WHEN Item_Width BETWEEN 100.01 AND 300 THEN 'W_100300'
WHEN Item_Width BETWEEN 300.01 AND 600 THEN 'W_300600'
WHEN Item_Width > 600.01 THEN 'W_abv600'
ELSE ''
END,
CASE
WHEN Item_Length BETWEEN 0 AND 300 THEN 'L_0300'
WHEN Item_Length BETWEEN 300.01 AND 600 THEN 'L_300600'
WHEN Item_Length BETWEEN 600.01 AND 900 THEN 'L_600900'
WHEN Item_Length > 900.01 THEN 'L_abv900'
ELSE ''
END;

This query categorizes the items based on width and length and sums the quantities received for each category. Adjust the table name (YourTable) to match your actual table name.


Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[dhrupal_shah]

Dear Saud,

problem is while i put column (sql column0 in matrix in value the result get as i expected (W_0100 and in value of that column). but while put it in row it show me 0.1, 0.2 .... (mean value of that field) ,while i want that W_0100 AND qty as my measure not want to show multiple row.

[saud968]

you might need to adjust how you’re structuring your query and how you’re using the matrix. Here are a few steps to help you:

Ensure Proper Grouping: Make sure your query groups the data correctly so that each category (e.g., W_0100) appears only once with the corresponding quantity.
Use Aggregation: When placing the column in the rows section, ensure that you’re using an aggregation function like SUM() to combine the quantities correctly.
Pivot the Data: If you’re using a tool like Power BI or Excel, you might need to pivot the data to get the desired format.
Here’s an example of how you might adjust your query:

SQL

SELECT
CASE
WHEN Item_Width BETWEEN 0 AND 100 THEN 'W_0100'
WHEN Item_Width BETWEEN 100.01 AND 300 THEN 'W_100300'
WHEN Item_Width BETWEEN 300.01 AND 600 THEN 'W_300600'
WHEN Item_Width > 600.01 THEN 'W_abv600'
ELSE ''
END AS Width_Category,
CASE
WHEN Item_Length BETWEEN 0 AND 300 THEN 'L_0300'
WHEN Item_Length BETWEEN 300.01 AND 600 THEN 'L_300600'
WHEN Item_Length BETWEEN 600.01 AND 900 THEN 'L_600900'
WHEN Item_Length > 900.01 THEN 'L_abv900'
ELSE ''
END AS Length_Category,
SUM(QtyRecv) AS Total_QtyRecv
FROM YourTable
GROUP BY
CASE
WHEN Item_Width BETWEEN 0 AND 100 THEN 'W_0100'
WHEN Item_Width BETWEEN 100.01 AND 300 THEN 'W_100300'
WHEN Item_Width BETWEEN 300.01 AND 600 THEN 'W_300600'
WHEN Item_Width > 600.01 THEN 'W_abv600'
ELSE ''
END,
CASE
WHEN Item_Length BETWEEN 0 AND 300 THEN 'L_0300'
WHEN Item_Length BETWEEN 300.01 AND 600 THEN 'L_300600'
WHEN Item_Length BETWEEN 600.01 AND 900 THEN 'L_600900'
WHEN Item_Length > 900.01 THEN 'L_abv900'
ELSE ''
END;

This query groups the data by width and length categories and sums the quantities received. When you use this result in your matrix, place Width_Category and Length_Category in the rows section and Total_QtyRecv in the values section.


Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[saud968]

@dhrupal_shah was the above helpful

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!