Question 374209 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – How to DISTINCT column value based on other column value

I have below table information

Batch_ID Items Time User
Batch_1 50 0.70 Abc
Batch_2 40 0.70 Abc
Batch_2 60 0.70 Abc

I want SQL code where I can calculate the SUM of Items and the SUM of Time but I want to DISTINCT the time value of the same Batch_ID.

The result that I should get is 150 for Items and 1.40 for time.

The results I got for the time value is 0.70.

Below example for the results I want to show:

User Sum(Items) Sum(Time)
Abc 150 1.40 
SELECT user_id, SUM(items), SUM(DISTINCT time) FROM items_p GROUP BY dayweek,user_id
Tags:

2

Answers


  1. 0

    Here is the query,

    res1 query takes the distinct TIME from table grouped by batch_id,user and then returns both batch_id and user

    res2 query takes sum of items grouped by user and then returns both user and sum of items.

    Finally do a inner join for both using user.

    SELECT res2.t_items,
       SUM(t_times) AS t_times,
       res1.USER
FROM   (SELECT DISTINCT TIME AS t_times,
                        USER,
                        batch_id
        FROM   batchdata
        GROUP  BY USER,
                  batch_id
        ORDER  BY t_times DESC) AS res1
       inner join (SELECT USER,
                          SUM(items) AS t_items
                   FROM   batchdata
                   GROUP  BY USER) AS res2
               ON res2.USER == res1.USER
    Login or Signup to reply.
  2. 0

    I hope this will help ^^

    SELECT [user] as 'User',SUM(items) as 'Sum(Items)',SUM(TIME) as 'Sum(Time)'
FROM (
    SELECT [user],SUM(items) AS items,SUM(DISTINCT time) AS TIME
    FROM your_table_name
    GROUP BY [user] ,batch_id
    ) AS t1
GROUP BY [user]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search