CODEWITHSUNDEEP
sqloracle-databaseaggregate
Ben
Ben

Reputation: 305

SQL aggregate functions and sorting

I am still new to SQL and getting my head around the whole sub-query aggregation to display some results and was looking for some advice:

The tables might look something like:

Customer: (custID,  name, address)
Account: (accountID, reward_balance)
Shop: (shopID, name, address)

Relational tables:

Holds (custID*, accountID*)
With (accountID*, shopID*)

How can I find the store that has the least reward_balance? (The customer info is not required at this point)

I tried:

SELECT accountID AS ACCOUNT_ID, shopID AS SHOP_ID, MIN(reward_balance) AS LOWEST_BALANCE
FROM Account, Shop, With
WHERE With.accountID = Account.accountID
AND With.shopID=Shop.shopID
GROUP BY
Account.accountID,
Shop.shopID
ORDER BY MIN(reward_balance);

This works in a way that is not intended:

ACCOUNT_ID | SHOP_ID | LOWEST_BALANCE
 1         |   1     |   10
 2         |   2     |   40
 3         |   3     |   100
 4         |   4     |   1000
 5         |   4     |   5000

As you can see Shop_ID 4 actually has a balance of 6000 (1000+5000) as there are two customers registered with it. I think I need to SUM the lowest balance of the shops based on their balance and display it from low-high.

I have been trying to aggregate the data prior to display but this is where I come unstuck:

SELECT shopID AS SHOP_ID, MIN(reward_balance) AS LOWEST_BALANCE
FROM (SELECT accountID, shopID, SUM(reward_balance) 
     FROM Account, Shop, With
     WHERE 
         With.accountID = Account.accountID
         AND With.shopID=Shop.shopID
         GROUP BY
             Account.accountID,
             Shop.shopID;

When I run something like this statement I get an invalid identifier error.

Error at Command Line : 1 Column : 24
Error report -
SQL Error: ORA-00904: "REWARD_BALANCE": invalid identifier
00904. 00000 -  "%s: invalid identifier"

So I figured I might have my joining condition incorrect and the aggregate sorting incorrect, and would really appreciate any general advice.

Thanks for the lengthy read!

Upvotes: 1

Views: 267

Answers (2)

spencer7593
spencer7593

Reputation: 108460

Approach this problem one step at time.

We're going to assume (and we should probably check this) that by least reward_balance, that refers to the total of all reward_balance associated with a shop. And we're not just looking for the shop that has the lowest individual reward balance.

First, get all of the individual "reward_balance" for each shop. Looks like the query would need to involve three tables...

SELECT s.shop_id
     , a.reward_balance
  FROM `shop` s
  LEFT
  JOIN `with` w
    ON w.shop_id = s.shop_id 
  LEFT
  JOIN `account` a
    ON a.account_id = w.account_id

That will get us the detail rows, every shop along with the individual reward_balance amounts associated with the shop, if there are any. (We're using outer joins for this query, because we don't see any guarantee that a shops is going to be related to at least one account. Even if it's true for this use case, that's not always true in the more general case.)

Once we have the individual amounts, the next step is to total them for each shop. We can do that using a GROUP BY clause and a SUM() aggregate.

SELECT s.shop_id
     , SUM(a.reward_balance) AS tot_reward_balance
  FROM `shop` s
  LEFT
  JOIN `with` w
    ON w.shop_id = s.shop_id 
  LEFT
  JOIN `account` a
    ON a.account_id = w.account_id
 GROUP BY s.shop_id

At this point, with MySQL we could add an ORDER BY clause to arrange the rows in ascending order of tot_reward_balance, and add a LIMIT 1 clause if we only want to return a single row. We can also handle the case when tot_reward_balance is NULL, assigning a zero in place of the NULL.

SELECT s.shop_id
     , IFNULL(SUM(a.reward_balance),0) AS tot_reward_balance
  FROM `shop` s
  LEFT
  JOIN `with` w
    ON w.shop_id = s.shop_id 
  LEFT
  JOIN `account` a
    ON a.account_id = w.account_id
 GROUP BY s.shop_id
 ORDER BY tot_reward_amount ASC, s.shop_id ASC
 LIMIT 1

If there are two (or more) shops with the same least value of tot_reward_amount, this query returns only one of those shops.

Oracle doesn't have the LIMIT clause like MySQL, but we can get equivalent result using analytic function (which is not available in MySQL). We also replace the MySQL IFNULL() function with the Oracle equivalent NVL() function...

SELECT v.shop_id
     , v.tot_reward_balance
     , ROW_NUMBER() OVER (ORDER BY v.tot_reward_balance ASC, v.shop_id ASC) AS rn
  FROM ( 
         SELECT s.shop_id
              , NVL(SUM(a.reward_balance),0) AS tot_reward_balance
           FROM shop s
           LEFT
           JOIN with w
             ON w.shop_id = s.shop_id 
           LEFT
           JOIN account a
             ON a.account_id = w.account_id
          GROUP BY s.shop_id
       ) v
HAVING rn = 1

Like the MySQL query, this returns at most one row, even when two or more shops have the same "least" total of reward_balance.

If we want to return all of the shops that have the lowest tot_reward_balance, we need to take a slightly different approach.

The best approach to building queries is step wise refinement; in this case, start by getting all of the individual reward_amount for each shop. Next step is to aggregate the individual reward_amount into a total. The next steps is to pickout the row(s) with the lowest total reward_amount.

Upvotes: 1

Amit Sukralia
Amit Sukralia

Reputation: 950

In SQL Server, You can try using a CTE:

  ;with cte_minvalue as
    (
     select rank() over (order by Sum_Balance) as RowRank,
     ShopId,
     Sum_Balance
      from (SELECT Shop.shopID, SUM(reward_balance) AS Sum_Balance 
         FROM 
             With
             JOIN Shop ON With.ShopId = Shop.ShopId
             JOIN Account ON With.AccountId = Account.AccountId
         GROUP BY
            Shop.shopID)ShopSum
      )
      select ShopId, Sum_Balance from cte_minvalue where RowRank = 1

Upvotes: 0

Related Questions