Menu
I have a field called "Users", and I want to run SUM() on that field that returns the sum of all DISTINCT records. I thought that this would work:
SELECT SUM(DISTINCT table_name.users)
FROM table_name
But it's not selecting DISTINCT records, it's just running as if I had run SUM(table_name.users).
What would I have to do to add only the distinct records from this field?
877
Yes, you can use COUNT() and DISTINCT together to display the count of only distinct rows. SELECT COUNT(DISTINCT yourColumnName) AS anyVariableName FROM yourTableName; To understand the above syntax, let us create a table.
In SQL, we use the SUM() function to add the numeric values in a column. It is an aggregate function in SQL. The aggregate function is used in conjunction with the WHERE clause to extract more information from the data.
Using DISTINCT in aggregationsYou can use DISTINCT when performing an aggregation. You'll probably use it most commonly with the COUNT function. In this case, you should run the query below that counts the unique values in the month column.
The SUM() function returns the total sum of a numeric column.
Use count()
SELECT count(DISTINCT table_name.users)
FROM table_name
This code seems to indicate sum(distinct ) and sum() return different values.
with t as (
select 1 as a
union all
select '1'
union all
select '2'
union all
select '4'
)
select sum(distinct a) as DistinctSum, sum(a) as allSum, count(distinct a) as distinctCount, count(a) as allCount from t
Do you actually have non-distinct values?
select count(1), users
from table_name
group by users
having count(1) > 1
If not, the sums will be identical.
41
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
