SQL Query with Distinct and Sum

SQL query with distinct and sum

SELECT color, fruit, sum(rating)
FROM medleys
GROUP BY color, fruit

Distinct is used to select distinct elements, nothing more, while you want to aggregate and for that you need GROUP BY and aggregation functions (SUM).

How to combine SELECT DISTINCT and SUM()

DISTINCT is not the clause you are looking for!

GROUP BY is.

The following query will return with all products and the total quantity for each one.

SELECT
  Product_NME
  , SUM(Quantity) AS TotalQuantity
FROM
  Products 
GROUP BY
  Product_NME

How to use DISTINCT and SUM in a same SQL query on MS SQL Express

Maxbe this will be one way to do it:

select distinct sum(price) over(partition by note), note
from tablename
where Archived = 0

Here is a demo on SQLServer

If I have understood you correctly you need distinct note values and only one sum for all of them ... then something like this:

select distinct note, (select sum(price) from tablename) sum_tot
from tablename
where Archived = 0

P.S. do add expected result....

Getting sum of a column that needs a distinct value from other column

I guess this is a job for a subquery. So let's take your problem step by step.

I'm trying to find all the rows in the balance column that are the same and have the same date,

This subquery gets you that, I believe. It give the same result as SELECT DISTINCT but it also counts the duplicated rows.

                SELECT COUNT(*) num_same_rows, balance, date
                  FROM `table`
                 WHERE a.datum BETWEEN '2021-01-01' AND '2021-09-01'
                 GROUP BY date, balance

and then find the sum of the balance column.

Nest the subquery like this.

SELECT SUM(balance) summed_balance, date
  FROM (
                SELECT COUNT(*) num_same_rows, balance, date
                  FROM `table`
                 WHERE a.datum BETWEEN '2021-01-01' AND '2021-09-01'
                 GROUP BY date, balance
       ) subquery
 GROUP BY date

If you only want to consider rows that actually have duplicates, change your subquery to

                SELECT COUNT(*) num_same_rows, balance, date
                  FROM `table`
                 WHERE a.datum BETWEEN '2021-01-01' AND '2021-09-01'
                 GROUP BY date, balance
                HAVING COUNT(*) >= 1

Be careful here, though. You didn't tell us what you want to do, only how you want to do it. The way you described your problem calls for discarding duplicated data before doing the sums. Is that right? Do you want to discard data?

Get distinct values and sum their respective quantities

You can use group by:

select ItemNo, sum(Qty) as QtyTotal
from QueryOutput q
group by ItemNo;

You can replace QueryOutput with a query that produces your example table.

Fiddle

---

Related Topics