Clever Geek Handbook

SQL -EX.ru query problem set # 28

I am trying to write a SQL query to resolve a question on www.sql-ex.ru (Q. 28), I got the correct result, but I got this error: "Your query failed in the third database check."

SQL query query:

Within two decimal digits, determine the average amount of paint per square.

SQL Notes:

The database schema consists of 3 tables:

utQ (Q_ID int, Q_NAME varchar (35)), utV (V_ID int, V_NAME varchar (35), V_COLOR char (1)),

utB (B_Q_ID int, B_V_ID int, B_VOL tinyint, B_DATETIME datetime). The utQ table includes a square identifier, the name of the square. Please note that black is not painted.

utV includes balloon ID, balloon name and paint color. The utB table displays information about drawing a square with a balloon and includes the square ID, balloon ID, paint amount, and paint time.

  • It should be noted that the balloon can have one of three colors: red (V_COLOR = 'R'), green (V_COLOR = 'G') or blue (V_COLOR = 'B');
  • any cylinder is initially filled with a volume of 255;
  • square color is determined according to the RGB rule, that is, R = 0, G = 0, B = 0 is black, while R = 255, G = 255, B = 255 is white;
  • any record in the utB table decreases the amount of paint in the B_VOL bottle and increases the amount of paint in a square by the same amount;
  • B_VOL must be greater than 0 and less than or equal to 255;
  • the amount of paint of the same color in one square cannot exceed 255;
  • the amount of paint in the bottle cannot be less than 0;
  • the drawing time (B_DATETIME) is specified with precision to the second, that is, it does not contain milliseconds.

Scheme

Scheme

My code

WITH x AS (SELECT b_q_id, b_v_id, ball.v_color, b_vol as vol FROM utb
JOIN utv ball
ON utb.b_v_id = ball.v_id)
, y AS (
SELECT b_q_id, v_color, totalcolor = CASE WHEN SUM(vol) > 255 THEN 255
ELSE SUM(vol) END FROM x
GROUP BY b_q_id, v_color),
z AS(
SELECT b_v_id, totalbcolor = 
CASE WHEN SUM(b_vol) > 255 THEN 255
ELSE SUM(b_vol)
END
FROM utb
GROUP BY b_v_id)
, a AS
(SELECT b_q_id, SUM(totalcolor) totalacolor FROM y GROUP BY b_q_id)
, b AS 
(SELECT b_q_id,b_v_id, totalacolor FROM a, z)
, c AS
(SELECT DISTINCT b.b_q_id, totalacolor FROM b
INNER JOIN utb
ON (b.b_q_id = utb.b_q_id AND b.b_v_id = utb.b_v_id))

SELECT 
CAST(CAST(SUM(totalacolor) AS NUMERIC(8,2)) 
/ (SELECT COUNT(*) FROM utq)AS NUMERIC(8,2))  
 FROM c

Correct answer

386.25

Comments My code is probably too long. But there is probably a shorter way to do it. But I still don't understand where I am going wrong. Please, help.

+3


source to share


3 answers


here is your code)

SELECT  CONVERT(NUMERIC(15,2),SUM(COALESCE(UB.B_VOL,0)) / CONVERT(FLOAT,COUNT(DISTINCT UQ.Q_ID)))
FROM    utQ AS UQ
        LEFT JOIN utB AS UB ON UQ.Q_ID = UB.B_Q_ID
        LEFT JOIN utV AS UV ON UB.B_V_ID = UV.V_ID


test below

enter image description here

+5


source


if you want to go to the help topics you can use this query:



SELECT cast(SUM(case when UB.B_VOL IS NULL then 0 else UB.B_VOL end) /cast(COUNT(DISTINCT UQ.Q_ID) as float) as decimal(8,2))FROM utQ AS UQ
    LEFT JOIN utB AS UB ON UQ.Q_ID = UB.B_Q_ID
0


source


Here's another solution using CASE WHEN ... THEN ... ELSE ... END

:

SELECT CONVERT(NUMERIC(15,2),
(
SELECT SUM(total_paint) * 1.0 / COUNT(Q_ID) AS avg_paint 
FROM
(
SELECT DISTINCT utQ.Q_ID, 
CASE
WHEN SUM(B_VOL) IS NULL
THEN 0
ELSE SUM(B_VOL)
END
AS total_paint
FROM utQ
LEFT JOIN utB ON utB.B_Q_ID = utQ.Q_ID
GROUP BY utQ.Q_ID
) AS tmp
)
)
0


source






More articles:


All Articles