How to Divide Sum in Number from Count in Having

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 an IN 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



Leave a reply



Submit