Dividing a SUM with a Count returns false results
You forgot to join your tables.
Try this:
SELECT SUM(ACCOUNT_COUNT) / COUNT(IS_TERMINATED)
FROM T_APPLICATIONS x inner join V_USERS v ON x.ID = v.ID
WHERE RESOURCE_TYPE='A';
And if you dont have a key to join then try this:
SELECT
(SELECT SUM(ACCOUNT_COUNT) FROM T_APPLICATIONS WHERE RESOURCE_TYPE='A') /
(SELECT COUNT(IS_TERMINATED) FROM V_USERS)
MySQL Percentage Divide count by sum
As I was indicating in my comment, both parts do not need to be subqueries.
SELECT name
, COUNT(*) AS total
, 100 * COUNT(*)/s.total_sum AS percentage
FROM games
CROSS JOIN (
SELECT COUNT(*) AS total_sum
FROM games
WHERE gameStatus = 'win'
AND created_at > '2018-03-01 12:02:26'
) AS s
WHERE gameStatus = 'win'
AND created_at > '2018-03-01 12:02:26'
GROUP BY name
;
Not sure if this will actually be any faster though; I wouldn't be surprised if MySQL ends up handling them in nearly identical manners.
Divide count by count(*) in SQL Server
Instead of
COUNT(CASE WHEN t.id is null THEN 1 END)/Count(*)
You can use
AVG(CASE WHEN t.id is null THEN 1.0 ELSE 0 END)
Dividing SUM By A Distinct Count and getting an error
Move the subquery to the FROM
clause:
SELECT SUM(DISTINCT CE.USD_NOTIONAL) / MAX(dey.cntd)
FROM CE CROSS JOIN
(SELECT COUNT(DISTINCT MARKET_DATE) as cntd
FROM DATA_EURO_YTD
WHERE MARKET_DATE >= DATE '2019-01-01'
) dey
WHERE CE.tradedate >= DATE '2019-01-01' AND
CE.exchange IN (SELECT EXCHANGE
FROM exchange EX
WHERE EX.FEED IN ('Y')
);
Notes:
- Moving the subquery to the
FROM
clause almost solves the whole problem. You still need an aggregation around the value. SELECT DISTINCT
is redundant in anIN
subquery.- I fixed the dates to use the
DATE
prefix for date literals.
SQL SUM values from column and dividide by number of rows
use COUNT
to get the totalNumber of rows.
SELECT SUM(voto) / (COUNT(*) * 1.0)
FROM tablename
WHERE id = numberHere
by adding * 1.0
on the query will allow decimal places on the result.
or simply as
SELECT AVG(voto)
FROM tablename
WHERE id = numberHere
Related Topics
Sql - Sum of Positive and Negative Numbers Using Subquery
Multiple Rows Output into Variables in MySQL
Truncate Seconds and Milliseconds in SQL
Sql Server: Check If Variable Is Null and Then Assign Statement for Where Clause
Extracting Data Between Two Delimiters in SQL Server
Formatting Numbers by Padding With Leading Zeros in SQL Server
Compare 2 Column Values in Same Table
Is There a Command to Test an SQL Query Without Executing It ( MySQL or Ansi SQL )
Postgresql Error: Fatal: Role "Username" Does Not Exist
Update Multiple Rows in a Table from Another Table When Condition Exists
Two Rows With the Same Id and Two Different Values, Getting the Second Value into Another Column
Fill a Field With the Values from Another Field in Ms Access
Multiple Query Same Table But in Different Columns MySQL
Remove Multiple Keys from Jsonb Column in One Statement
Remove Numbers from String SQL Server