I have a database I use for a debating competition I am trying to sort the standings out to see which schools will play off in the finals at the end of the semester.
I have this working as shown below however I am not able to round the ptc field, it currently returns up to 4 decimal places eg 0.6667 if they have won 2 out of 3 debates.
SELECT t.id,
t.name AS name,
SUM(t.id = d.winnerid) AS w,
SUM(t.id != d.winnerid) AS l,
SUM(t.id = d.winnerid)/(SUM(t.id = d.winnerid)+SUM(t.id != d.winnerid)) AS ptc
FROM debates AS d
JOIN teams AS t ON t.id IN (d.hostid, d.visitid)
WHERE d.visitid != -1
AND d.debatedate < CURDATE()
GROUP BY t.id
ORDER BY ptc DESC
I believe I have to use decimal(2,2) here however I am not able to get the syntax right I have tried a few diferent things like
SUM(t.id = d.winnerid)/(SUM(t.id = d.winnerid)+SUM(t.id != d.winnerid)) AS ptc decimal (2,2)
Am happy to provide more information about the tables if required but I don't think it is required?
SQL Server SUM() Function: Get Total In SQL Server, the SUM() function returns the sum of all or distinct values in a given expression. It can only be used with the numeric type column or expression. The NULL values are ignored.
SQL Server ROUND() Function The ROUND() function rounds a number to a specified number of decimal places. Tip: Also look at the FLOOR() and CEILING() functions.
The ROUND() function is used to round a numeric field to the number of decimals specified.
Below are four functions that can be used to format a number to two decimal places in SQL Server. The most obvious way to do it is to convert the number to a decimal type. Two functions that can do this for us is CAST () and CONVERT ().
We can use the SUM () function in SQL to calculate the total value of the columns of the tables or the total of expressions that involve column values and even calculate the total value of columns in the grouped manner using GROUP BY statement. This is a guide to SQL SUM ().
Example. Round the number to 2 decimal places, and also use the operation parameter: SELECT ROUND (235.415, 2, 1) AS RoundValue; Try it Yourself ».
Another way to format a number to two decimal places is to use the STR () function: This function returns character data converted from numeric data. The character data is right-justified, with a specified length and decimal precision. The first argument is an expression of float data type with a decimal point.
try this.
SELECT ROUND(SUM(cash), 2)
FROM <tablename>
If you are getting no results, then there must be a null value, try this instead.
SELECT ROUND(SUM(cash), 2)
FROM<tablename> a
WHERE cash IS NOT NULL
Here is a simple demo of it :
Update :
SELECT round( ROUND(SUM(p.prod_price = l.prod_unit_price), 2)
/ROUND(SUM(p.prod_id = l.prod_id), 2),2)
FROM b2b.product_master p
join b2b.move_cart_item_master l;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With