Multiple Aggregate Functions in One SQL Query from the Same Table Using Different Conditions

Multiple aggregate functions in one SQL query from the same table using different conditions

SELECT
    E.EMPID
    ,SUM(ABSENCE.HOURS_ABSENT) AS ABSENT_TOTAL
    ,SUM(case when year(Date) = 2010 then ABSENCE.HOURS_ABSENT else 0 end) AS ABSENT_YEAR

FROM
    EMPLOYEE E

    INNER JOIN ABSENCE ON
        ABSENCE.EMPID = E.EMPID

GROUP BY
    E.EMPID

HAVING
    SUM(ATOTAL.HOURS_ABSENT) > 10
    OR SUM(case when year(Date) = 2010 then ABSENCE.HOURS_ABSENT else 0 end) > 3

edit:

It's not a big deal, but I hate repeating conditions so we could refactor like:

Select * From
(
    SELECT
        E.EMPID
        ,SUM(ABSENCE.HOURS_ABSENT) AS ABSENT_TOTAL
        ,SUM(case when year(Date) = 2010 then ABSENCE.HOURS_ABSENT else 0 end) AS ABSENT_YEAR

    FROM
        EMPLOYEE E

        INNER JOIN ABSENCE ON
            ABSENCE.EMPID = E.EMPID

    GROUP BY
        E.EMPID
    ) EmployeeAbsences
    Where ABSENT_TOTAL > 10 or ABSENT_YEAR > 3

This way, if you change your case condition, it's in one spot only.

Multiple aggregate functions in SQL query

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

Next, learn what COUNT() does. It counts the number of non-NULL values. So, your expressions are going to return the same value -- because f.UID and s.UID are never NULL (due to the JOIN conditions).

The issue is that the different dimensions are multiplying the amounts. A simple fix is to use COUNT(DISTINCT):

SELECT p.ID, COUNT(DISTINCT s.UID) AS SubCount, COUNT(DISTINCT f.UID) AS FollowCount 
FROM page p JOIN
     subs s
     ON p.ID = s.ID JOIN
     followers f 
     ON s.ID = f.ID 
GROUP BY p.ID;

The inner joins are equivalent to the original query. You probably want left joins so you can get counts of zero:

SELECT p.ID, COUNT(DISTINCT s.UID) AS SubCount, COUNT(DISTINCT f.UID) AS FollowCount 
FROM page p LEFT JOIN
     subs s
     ON p.ID = s.ID LEFT JOIN
     followers f 
     ON p.ID = f.ID 
GROUP BY p.ID;

Optimal SQL to perform multiple aggregate functions with different group by fields

Is it slow because it's evaluating each row separately with the subquery in the select statement? It may be operating as a correlated subquery.

If that's the case it might be faster if you get the values out of a join and go from there -

Select city, t1.item, (COUNT(t1.item) / MAX(t2.it_count)) AS pen_ratio
from records t1
JOIN (SELECT item, count(item) AS it_count
      FROM records
      group by item) t2
        ON t2.item = t1.item
GROUP BY city, t1.item

Updated some errors and included the fiddle based off the starting point from xQbert. I had to CAST as float in the fiddle, but you may not need to CAST and use the above query in yours depending on datatypes.

I believe this follows the intent of your original query.

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=d77a715175159304b9192a16ad903347

SQL Filter two aggregate functions with different conditions

You can use conditional aggregation:

SELECT airline_name,
       (AVG(CASE WHEN fl_date BETWEEN '2017-07-24' and '2017-07-31' THEN arr_delay_new END) -
        AVG(CASE WHEN fl_date BETWEEN '2017-07-01' and '2017-07-23' THEN arr_delay_new END)
       ) as AVG_DIFF
FROM Flight_delays F JOIN
     Airlines A
     ON A.airline_id = F.airline_id
GROUP BY airline_name;

This assumes that arr_delay_new has a type that can be averaged. Some databases are reluctant to do averages on date/times directly.

Combining two or more different SELECT queries to same table with different conditions in PostgreSQL

Just write this as one query:

select sum(price) as lucro_esperado, count(*) as tarefas_abertas
from tasks
where extract(month from enddate) = 12 and
      extract(year from enddate) = 2019

I would advise you to change the where clause to:

where enddate >= '2019-12-01' and
      enddate < '2020-01-01'

This allows the database to use an index on enddate (if available). Also, removing the function calls on the column helps the optimizer.

EDIT:

I see, the two date parameters are different. Just use conditional aggregation:

select sum(case when enddate >= '2019-12-01' and enddate < '2020-01-01' then price end) as lucro_esperado,
       sum(case when date_added >= '2019-12-01' and date_added < '2020-01-01' then 1 else 0 end) as tarefas_abertas
from tasks;

multiple conditions on a sql Aggregate Function

Seems like you just need to add a , and the field name to the group by.
You may want to add identifier_two to the select as well in case the where clause needs to be updated to account for both identifiers. (Likely)

SELECT identifier_one
     , identifier_two  --not required but may be useful as you seem to filter on identifier_one later; maybe you also need the 2nd one...
     , SUM(IF(YEAR(`date`) = YEAR(CURDATE()), quantity, 0)) AS current_year,
     , SUM(IF(YEAR(`date`) = YEAR(CURDATE()) AND MONTH(`date`) = MONTH(CURDATE() ), quantity, 0)) AS current_month
     , SUM(IF(YEAR(`date`) = YEAR(CURDATE()) - 1, quantity, 0)) AS last_year
     , SUM(IF(YEAR(`date`) = YEAR(CURDATE()) AND MONTH(`date`) = MONTH(CURDATE()) - 1, quantity, 0)) AS last_month
FROM sales
GROUP BY identifier_one, identifier_two --this is what you're missing

SQL Query - How to apply different conditions (Where clause?) to specific aggregated columns

Using conditional aggregation, which in most databases looks like this:

SELECT Test, 
       SUM(CASE WHEN TESTSTATUS = 'P' THEN TESTPRICE END) AS PendingRev,
       SUM(CASE WHEN TESTSTATUS = 'S' THEN TESTPRICE END) AS StartedRev
FROM REQUEST
GROUP BY TEST
ORDER BY TEST;

Can we use same aggregate function more than once on same table field or column using Different filter conditions?

You can use an aggregate function with a CASE:

SELECT Date1,
  CC,
  BU, 
  SUM(case when mode = '011' then Amount end) Mode011,
  SUM(case when mode = '012' then Amount end) Mode012,
  SUM(case when mode = '013' then Amount end) Mode013,
  SUM(case when mode = '014' then Amount end) Mode014
FROM MainTable
GROUP BY CC,BU,Date1;

Or you can use the PIVOT function:

select date1, CC, BU,
  [011] Mode011, 
  [012] Mode012, 
  [013] Mode013, 
  [014] Mode014
from
(
  select date1, CC, BU, mode, amount
  from maintable
) src
pivot
(
  sum(amount)
  for mode in ([011], [012], [013], [014])
) piv

Aggregate on multiple conditions: convert to a single query

I prefer to have a query like this, because what if there are some places that have no reservation yet or may have multiple reservations already. Still it's safe to calculate the SUM.

SELECT  d.*, a.total_confirmed, b.total_paid, c.total_paid_unconfirmed
FROM    places d
        LEFT JOIN
        (
            SELECT places.id, places.name, COUNT(reservations.*) as total_confirmed
            FROM reservations
              INNER JOIN places ON places.id = reservations.place_id
            WHERE
              reservations.confirmed_at IS NOT NULL
            GROUP BY places.id, places.name
        ) a ON d.id = a.id
        LEFT JOIN
        (
            SELECT places.id, places.name, COUNT(reservations.*) as total_paid
            FROM reservations
              INNER JOIN places ON places.id = reservations.place_id
            WHERE
              reservations.paid_at IS NOT NULL
            GROUP BY places.id, places.name
        ) b ON d.id = b.id
        LEFT JOIN
        (
            SELECT places.id, places.name, COUNT(reservations.*) as total_paid_unconfirmed
            FROM reservations
              INNER JOIN places ON places.id = reservations.place_id
            WHERE
              reservations.paid_at IS NOT NULL AND reservations.confirmed_at IS NULL
            GROUP BY places.id, places.name
        ) c ON d.id = c.id

---

Related Topics