Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

set decimal places of sum in sql

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?

like image 586
ak85 Avatar asked Dec 28 '13 09:12

ak85


People also ask

How do you SUM decimals in SQL?

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.

How do I fix decimal places in SQL?

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.

How do you round a SUM in SQL?

The ROUND() function is used to round a numeric field to the number of decimals specified.

How to format a number to two decimal places in SQL Server?

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 ().

How to use the sum () function in SQL?

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 ().

How to round a number to 2 decimal places in Excel?

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 ».

How to format a number to two decimal places in Python?

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.


1 Answers

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;
like image 87
Java Curious ღ Avatar answered Oct 07 '22 11:10

Java Curious ღ