CODEWITHSUNDEEP
sqlgroup-bysubquerymaxgreatest-n-per-group
Lyaso
Lyaso

Reputation: 155

sql query about latest date

I have these two tables.

UserProfiles(userid, attr , value)
InformationValues(attr , dateOfValue, price)

Table content:

Userprofiles for userid ='ann'

enter image description here

Informationvalues

enter image description here

Now I have to select for each attr the latest date and calculate the sum for the price for each attr the userid 'ann' has.

enter image description here

The sum price for the userid 'ann' of each attr price for the latest date would be then 3,2.

What I have so far

    select sum(iv.price * (count(distinct(u.attr)))) 
from userprofiles u , informationvalues iv
    where iv.attr = u.attr and u.userid ='ann'
 and  iv.dateofvalue = (select max(dateofvalue) from informationvalues)

I don't know what I am missing to get the value 3.2 for the userid 'ann'.

Upvotes: 0

Views: 43

Answers (1)

GMB
GMB

Reputation: 222382

You would need to correlate the subquery to the outer query, so it gives you the latest date per attribute rather than the overal latest date.

Also it is unclear to me why you need that computation with count(distinct ...) in the outer query.

Side note: always use modern, standard join syntax (with the on keyword) rather than implicit joins (with a comma in the from clause).

I would suggest:

select sum(iv.price) total_price 
from userprofiles u 
inner join informationvalues iv on iv.attr = u.attr   -- standard join syntax
where 
    u.userid ='ann'
    and iv.dateofvalue = (
        select max(iv1.dateofvalue) 
        from informationvalues iv1 
        where iv1.attr = iv.attr                       -- correlation
    )

Upvotes: 1

Related Questions