Get Highest amount , count of products / users

Question

id | user_id | prd_id | amnt | dis 
1  |   1     |  10    |  200 | 23
2  |   2     |  10    |  300 | 11
3  |   3     |  20    |  100 | 26
4  |   2     |  20    |  50  | 12
5  |   4     |  30    |  100 | 22
6  |   2     |  40    |  600 | 18
7  |   2     |  30    |  100 | 16

I want 2 result from above table :

First by prod_id as below

prd_id |  user_id  | cont |   highestamt | disc
10     |   2       |  2   |   300        | 11
20     |   3       |  2   |   100        | 26
30     |   4       |  2   |   100        | 22
40     |   2       |  1   |   600        | 18

Second by user_id as below:

user_id | cont |  bid on prd_id    | winner on bid prod_id |  
1       |  1   |   10              |  -                    |   -
2       |  4   |   10,20,30,40     |  10,40                |
3       |  1   |   20              |  20                   |
4       |  1   |   30              |  30                   |

UPDATE : ex: above : user_id = 2 has bid on product 10,20,30,40 ( bid on prd_id ) hence his bidding cont = 4 ...and out of which he is winner in 10,40 ( winner on bid prod_id ) ..WHY ONLY 10,40 and not 30 ...bcz user_id =4 has bid on prd=30 with amt =100 and user_id =2 with amt=100 ..but first bid was made by user=4 on prd=30 hence he is winner for prd=30 ( for same amt )

Tried below query for by prd_id but it giving me some wrong result.

SELECT `prd_id`, `user_id` , count('prd_id') as cont , max(`amnt`) as highestamt,disc
FROM `proddtails` 
group by `prd_id` order by `prd_id`

above query result as below : ( user_id,disc not coming proper )

prd_id |  user_id  | cont |   highestamt | disc
10     |   2       |  2   |   300        | 11
20     |   2       |  2   |   100        | 11
30     |   2       |  1   |   100        | 11
40     |   2       |  1   |   600        | 11

For second by user_id I am not getting what will be query.

Thanks

UPDATE :

THANKS FOR HARSHIL : http://www.sqlfiddle.com/#!9/5325a6/5/1

but after some more entry i found this bug : http://www.sqlfiddle.com/#!9/e04063/1 for second : for user_id but works well for prd_id (first query )

user_id  cont   bid_on_prd_id   winner_on_bid_prod_id
1         1         10                  (null)
2         4     10,20,40,30            10,40,30
3         1        20                     20
4         1        30                     30

but i want as below :

without null user_id

user_id  cont   bid_on_prd_id   winner_on_bid_prod_id
2         4     10,20,30,40             10,40
3         1        20                     20
4         1        30                     30

with null user_id ( but in my wamp server i don't see null in winner_on_bid_prd_id for user_id =1 , i get value 10 instead of null )

user_id  cont   bid_on_prd_id   winner_on_bid_prod_id
1         1         10                  (null)
2         4     10,20,30,40             10,40
3         1        20                     20
4         1        30                     30

if i do group by prd_id,user_id , disc ... then result give me output without group by prd_id ..inshort cont is 1 for all and it list me all value — user3209031, Oct 30 '17 at 18:09
Use `GROUP_CONCAT` to get a comma-separated list from a group. — Barmar, Oct 30 '17 at 18:19
so how you want get values for `prd_id` if you have different `user_id` and `disc` for every `prd_id` value? — Artem Ilchenko, Oct 30 '17 at 18:19
Why is it `10,40` for user 2? Product 10 has `amnt = 300`, 40 has `amnt = 600`. — Barmar, Oct 30 '17 at 18:22
See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the row with highest amount for each user. — Barmar, Oct 30 '17 at 18:23
@Barmar sir ,,, user_id = 2 has bid on product 10,20,40 ( bid on prd_id ) hence his bidding cont = 3 ...and out of which he is winner in 10,40 ( winner on bid prod_id ) ..same goes for other users..hope i have made it more clear now — user3209031, Oct 30 '17 at 18:24
Oh, I see. Not the highest bid per user, the highest bid per product, then regrouped by user. — Barmar, Oct 30 '17 at 18:25
@Barmar ... For prd_id : i got answer from @ Harshil.... waiting for second by user_id — user3209031, Oct 30 '17 at 18:26
@Barmar sir .. for user_id ... for user = 1..he is not winner hence last column should be null or - ... but i am getting there value as 10 under winner_on_bid_prod_id ..... user_id = int(11) , prd_id = int(11) , amnt = double — user3209031, Oct 30 '17 at 19:25

Harshil Doshi · Accepted Answer · 2017-10-31T09:18:00.433

4

For prd_id:

select t1.prd_id,t1.user_id,
 (select count(*) from tablename where prd_id = t1.prd_id)as cont,
t1.amnt as highststatment,
t1.dis as disc
from tablename t1
where (t1.prd_id,t1.amnt) in
(select prd_id, max(amnt) from tablename group by prd_id)
group by t1.prd_id;

For usr_id:

    select t1.user_id,
       count(*) as cont,
       Group_concat(t1.prd_id separator ',') as bid_on_prd_id,
       (select Group_concat(distinct t2.prd_id separator ',')
        from tablename t2 
        where t2.user_id = t1.user_id 
        and (t2.id) in 
                    (select min(id) from tablename
                        where (prd_id,amnt) in 
                                 (select prd_id,max(amnt) from tablename group by prd_id)
                      group by prd_id
                     )
         ) as winner_on_bid_prod_id
from tablename t1
group by t1.user_id
having winner_on_bid_prod_id IS NOT NULL;

Click here for UPDATED DEMO

edited Oct 31 '17 at 09:18

answered Oct 30 '17 at 18:08

Harshil Doshi

3,497
3
14
37

Yeah its working For prd_id ( will test with more multiple data ...but now its showing as need result ).... and sir second for user_id ... can you help me in that too – user3209031 Oct 30 '17 at 18:18
Please try the query for user_id. – Harshil Doshi Oct 30 '17 at 18:41
@Barmar thank you for the correction as well as fiddle. – Harshil Doshi Oct 30 '17 at 18:53
1

Thanks harshil and barmar sir ... you guys are genius ... i cant think of that above query ...and you guys made it so easy ....learning will be till death ...:) ....thanks guys .... – user3209031 Oct 30 '17 at 18:59
@user3209031 Always glad to help. 2nd query was tough a little bit. Thank you for a challenging question. (already did +1 for that). Feel free to ask the doubt(s), if you have any. – Harshil Doshi Oct 30 '17 at 19:03
@Harshil ...sure will test more ..and if any query will let you know ....thanks once again – user3209031 Oct 30 '17 at 19:05
@Harshil i found one issue for user_id ... for user = 1..he is not winner hence last column should be null or - ... but i am getting there value as 10 under winner_on_bid_prod_id – user3209031 Oct 30 '17 at 19:14
u r demo so (null) for user = 1.... but same query result me 10 under winner_on_bid_prod_id.... i need null or blank ...how i can – user3209031 Oct 30 '17 at 19:20
user_id = int(11) .... prd_id - int(11) .....amnt = double ....why ... i am not getting null – user3209031 Oct 30 '17 at 19:22
1

let me check this. – Harshil Doshi Oct 30 '17 at 19:29
its bcz i have used amnt = double and use have use amnt = int ... ?? ...ok ,,,can you please help me with only not null users.... that would be great ,,, or if possible both solution – user3209031 Oct 30 '17 at 19:31
Are you getting `10,40` as winner for user_id 2? – Harshil Doshi Oct 30 '17 at 19:48
Please run this query separately and comment the result here: `select Group_concat(distinct t2.prd_id separator ',') from tablename t2 where t2.user_id = 1 and (prd_id,amnt) in (select prd_id,max(amnt) from tablename group by prd_id);` – Harshil Doshi Oct 30 '17 at 19:57
@Harshil ABOVE comment query return NULL... but the answer query show me value 10 ... and for user_id = 2 i get proper result as 10,40 only user_id = 1 is not showing null ... can u provide me without null result ..means ..only winner thats ...user_id = 2 , user_id = 3 and user_id =4 ...show only this result ... – user3209031 Oct 31 '17 at 04:53
bro one more issue ....please check : http://www.sqlfiddle.com/#!9/c28bd/1 – user3209031 Oct 31 '17 at 05:15
for user_id = 4 , and user_id = 2 ..they both have bid on prod=30 with same amount=600 ...but first bidding was made by user_id = 4 hence he will be winner (user_id =4 ) and not user_id =2 ... but result showing both as winner ...and can u please show only winner result ,,,no null result...have marked as UN-answered right now... – user3209031 Oct 31 '17 at 05:20
1

yeah i have check its working now ...but just need to be more confirm ... – user3209031 Oct 31 '17 at 11:55

Get Highest amount , count of products / users

1 Answers1