Query Table ID based on Aggregate function in Postgres

postgres aggregate functions
postgres aggregate functions first
postgres group by
json_object_agg
postgres aggregate arrays
postgres window functions
must appear in the group by clause or be used in an aggregate function
postgres create aggregate

I have two tables namely Activity and Cards,

    **Activity**
+----+--------+--------+
| ID | Number | Amount |
+----+--------+--------+
|  1 |  12345 |    100 |
|  2 |  12134 |    200 |
|  3 |  12345 |    600 |
|  4 |  15647 |     50 |
|  5 |  12134 |    202 |
+----+--------+--------+

       **Cards** 
+----------+------------+
|  Number  |    Type    | 
+----------+------------+
|  12345   |  visa      |
|  12134   |  mastercard|
|  15647   |  diners    |
+----------+------------+

I would like to query the Transaction ID that have the most expensive amount among all transactions using same type card.

+----+
| ID |
+----+
|  3 |  (ID 3 have the Highest Amount transacted on card type Visa)
|  4 |  (ID 4 have the Highest Amount transacted on card type Master)
|  5 |  (ID 5 have the Highest Amount transacted on card type diners)
+----+

Can this be done with and without Aggregate query ? I have managed to query the Card Type and its corresponding highest amount spent in a single transaction

SELECT c.Type, MAX(a.Amount) AS Highest_Transaction
       FROM Cards AS c, Activity AS a
       WHERE c.Number = a.Number
       GROUP BY c.Type

But not sure how to query Only ID from Activity Table while using 'Type' as a GROUP by parameter.

Postgres offers distinct on which is very appropriate for this query:

select distinct on (c.type) a.id
from cards c join
     activity a
     on c.number = a.number
order by c.type, a.amount desc;

I've only included the activity id, but you can include as many other columns as you want.

EDIT:

If you want all rows in the event of ties, then use window functions:

select a.id
from (select c.type, a.id, max(a.amount) over (partition by c.type) as max_amount
      from cards c join
           activity a
           on c.number = a.number
     ) ca
where max_amount = amount;

Documentation: 9.5: Aggregate Functions, Aggregate functions compute a single result from a set of input values. The built-​in normal aggregate functions are listed in Table 9-49 and Table 9-50. match exactly expressions given in the GROUP BY clause of the associated query level. In these cases, the GROUP BY clause divides the result set into groups of rows and the aggregate functions perform a calculation on each group e.g., maximum, minimum, average, etc. You can use aggregate functions as expressions only in the following clauses: SELECT clause. HAVING clause. PostgreSQL aggregate functions examples. Let’s use the film table in the sample database for the demonstration.

You can try below query -

SELECT ID
FROM activity a
INNER JOIN (SELECT Num, MAX(Amount) Amt
            FROM activity
            GROUP BY Num) amt
ON a.Amount = amt.Amt;

Here is the fiddle - https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=31859b70c7b532ffe5b33de0c1f2cd88

Documentation: 9.4: Aggregate Functions, An aggregate function computes a single result from multiple input rows. However, as is often the case the query can be restated to accomplish the desired result, Each aggregate result is computed over the table rows matching that city. To create a zero-argument aggregate function, write * in place of the list of argument specifications. (An example of such an aggregate is count(*).) base_type. In the old syntax for CREATE AGGREGATE, the input data type is specified by a basetype parameter rather than being written next to the aggregate name. Note that this syntax allows only

Unfortunately it's impossible to receive correct result in one query, that's why you may consider an option to create denormalized temporary table and then run query which returns correct result.

Step 1:

CREATE TEMPORARY TABLE tmp AS
SELECT a.ID, a.Number, a.Amount, c.Type
FROM Cards AS c
LEFT JOIN Activity AS a ON c.Number = a.Number

Step 2:

SELECT ID
FROM tmp AS t1
INNER JOIN (
  SELECT Type, MAX(Amount) AS Highest_Transaction
  FROM tmp
  GROUP BY Type
) AS t2 ON t1.Type = t2.Type AND t1.Amount = t2.Highest_Transaction

Result:

 id
----
  5
  3
  4
(3 rows)

You can check this queries out at sqlfiddle.

An Overview of PostgreSQL Aggregate Functions By Examples, This tutorial shows you how to use the PostgreSQL aggregate functions such as Let's use the film table in the sample database for the demonstration. To calculate the average replacement cost of the Drama films whose category id To get the films that have the maximum replacement cost, you use the following query:. So, the following query is wrong: SELECT name, count(1) FROM products GROUP BY category; And will return the error: “column “products.name” must appear in the GROUP BY clause or be used in an aggregate function”. But, this query is perfectly fine: SELECT string_agg(name, ', '), count(1) FROM products GROUP BY category;

If Type is not unique in your table cards, you can join it first and group by Type as you suggested:

SELECT (array_agg(activity.ID ORDER BY activity.Amount DESC))[1] AS id
FROM activity
LEFT JOIN cards ON activity.Number = cards.Number
GROUP BY cards.Type;

The workaround is to build an ordered array for each card, but just select the highest id.

PostgreSQL GROUP BY, For each group, you can apply an aggregate function e.g., SUM() to calculate the query gets data from the payment table and groups the result by customer id. rows in the payment table based on staff_id and use the COUNT() function to  SELECT ( SELECT file_id FROM "table" WHERE file_id IS NOT NULL LIMIT 1 ); There is no way to optimize the query as you wrote it, because the aggregate function is a black box to PostgreSQL.

One approach is to use row_number()

SELECT
    id
  , type
  , amount
FROM (
    SELECT
        c.type
      , a.id
      , a.amount
      , ROW_NUMBER() OVER (PARTITION BY c.type ORDER BY a.amount DESC) AS rn
    FROM cards c
    JOIN activity a ON c.number = a.number
    ) d
WHERE rn = 1

PostgreSQL COUNT Function: Counting Rows That Satisfy a , The COUNT() function is an aggregate function that allows you to get the number If you use the COUNT(*) function on a big table, the query will be slow. payments into groups based on customer id, and use the COUNT() function to count  Note the order by id asc in the aggregate. Because a greatest_running_total function would require its inputs to be ordered to be correct, it is vital that we include this clause. Custom Aggregates. The greatest_running_total function doesn't exist, but PostgreSQL gives us the functionality to create our own aggregate functions.

PostgreSQL SUM Function, This tutorial shows you how to use PostgreSQL SUM function to calculate the sum of We will use the payment table in the sample database to demonstrate the functionality of the Let's calculate the sum of payment paid by customer id 2000. The following query illustrates the SUM function with the COALESCE function:. The name of the state transition function to be called for each input row. For a normal N-argument aggregate function, the sfunc must take N+1 arguments, the first being of type state_data_type and the rest matching the declared input data type(s) of the aggregate. The function must return a value of type state_data_type. This function takes the current state value and the current input data value(s), and returns the next state value.

PostgreSQL AVG function, The AVG() function is one of the most commonly used aggregate functions in PostgreSQL. In the query, we joined the payment table with the customer table using inner join. You can use the AVG function in the HAVING clause to filter the group based CREATE TABLE t1 ( id serial PRIMARY KEY, amount INTEGER );. Introduction to Aggregate Function in PostgreSQL. PostgreSQL aggregate functions are used to compute the set of input values in one result. It produced a single result for an entire group of tables. PostgreSQL aggregate functions used to produce a summarized set of results. They return results based on a group of rows set. Aggregate functions will treat all rows of a table as a group by default.

PostgreSQL MAX Function: Get Maximum Value in a Set, PostgreSQL MAX function is an aggregate function that returns the maximum value in query gets the maximum amount paid by customers in the payment table: It has four columns: 1 column to store user id and other three columns to store  mysql,sql Basically i want to inner join 3 tables and the case is understood by the query itself as follows. Tables: A has 2 columns column 1 and column 2 B has 2 columns column 3 and column 4 C has 3 columns column 5,column 6 and column 7 Query: select Merging two tables into new table by ID and date

Comments
  • What have you tried so far please also add your query
  • But this print out only One row in the case , lets say we have Two activity with same amount on same card type. Ideally we should show both the transaction activity
  • @AsheshNair . . . If you want all rows in the event of duplicates, then window functions are more appropriate.
  • Please do: insert into activity values (6, 1, 600); and insert into cards values (1, 'diners'); Will your query return correct result?