More with SQL: January 2025

January 09, 2025

SQL Query output in graphical representation

SQL Query output in graphical representation:

In SQL query sample I am using some pre defined function to show output in graphical output. Here, we have two important tables Product and OrderDetail that returns sale quantity and sale amount of each product. and I need to rate the product based on the sale quantity.

Output SQL:

WITH salesgraphical AS (SELECT CONVERT(VARCHAR(10), productname)
          ProductName, Sum(quantity) ActualSale, ( Round(( Sum(quantity) / 25.00
          ), 0) ) * 25 RoundedSaleBy25, Round(( ( Round(( Sum(quantity) / 25.00
          ), 0) ) * 25 ) / 100, 0) fullRounds, CASE Round(( ( Round((
          Sum(quantity) / 25.00 ), 0) ) * 25 ) / 100, 2) - Floor(( ( Round((
          Sum(quantity) / 25.00 ), 0) ) * 25 ) / 100) WHEN 0.25 THEN 2 WHEN 0.5
          THEN 3 WHEN 0.75 THEN 4 ELSE 1 END ExtraRound FROM orderdetail OD JOIN
          productmaster PM ON OD.productid = PM.productid GROUP BY productname)
          SELECT productname, actualsale, Concat(Replicate(N'🌑', fullrounds -
          1), Choose(extraround, N'🌑', N'🌔', N'🌓', N'🌒'), Replicate(N'🌕', 5
          - fullrounds)) graphicalOutput FROM salesgraphical;

You will learn the use of the following functions:

How to use the WITH clause to create a Common Table Expression (CTE) named salesgraphical.
Use of Round () function to rounded on 100's part or 25'th part of values.
Use of Choose function to select a value based on the index provided.
Use of Replicate function to repeat a value a specified number of times.
Use of Concat function to concatenate multiple strings together.