Re: Query with no-correspondence results

I have not read all 18 responses, so if I am off base I apologize.

I believe that you want to avg the votes (by gender) of each unique
combination of thing and context.

(First take gender out of the equation)
In order to be able to get the avg votes for the combination of
potatoes and flavor as compared to potatoes and smells you have to
give each combo a unique value and then group on that value.

for example potato and flavor could be =1 and potatoes and smells =2 ,
let's call this combo attribute column

Then you can group by the unique combo attribute, 1,or 2 in this
example

select combo_attribute,COALESCE( AVG( vote ) , 0.0 ) AS average_vote
from
mydatabase_votecontext AS md
group by combo_attribute

You can then join the 3 tables on the user id , no outsr join
necessary and add gender

select gender,combo_attribute,COALESCE( AVG( vote ) , 0.0 ) AS
average_vote from
mydatabase_votecontext a, mydatabase_user b, mydatabase_facebookuser
c
where a.id=c.id
group by combo_attribute,gender

On Dec 26, 11:00 pm, wgis <alpha.sh...@gmail.com> wrote:
> Sorry for digging this up but I have a more complex funcionality to
> implement and I was hoping you could help
>
> I had
>
> mydatabase_votecontext
> (id, name)
> (1, Flavour)
> (2, Smell)
> (3, Usability)
> (4, Size)
>
> mydatabase_vote
> (id, thing, context, user, vote)
> (1, Potatoes, Flavour, Me, 2.0)
> (2, Potatoes, Smell, Me, 4.3)
> (3, Potatoes, Usability, Me, 4.0)
> (4, Carrots, Flavor, Me, 3.0)
> (5, Cars, Smell, Me, 4.2)
> (6, Cars, Usability, Me, 4.9)
>
> and now I also have:
>
> mydatabase_user
> (id, ......)
>
> mydatabase_facebookuser)
> (id, user_id, gender, ...)
>
> I would like to query the average votes by gender :O
>
> I thought if the original was
>
> SELECT mb.id, mb.name, COALESCE( AVG( vote ) , 0.0 ) AS average_vote
> FROM mydatabase_votecontext AS md
> LEFT JOIN restudante_vote ON ( mydatabase_vote.context_id = md.id
>                                         AND thing_id = 1)
> GROUP BY thing_id, name
> ORDER BY md.id
>
> then
>
> SELECT md.id, md.name, COALESCE( AVG( vote ) , 0.0 ) AS average_vote
> FROM mydatabase_votecontext AS md
> LEFT JOIN restudante_vote ON ( mydatabase_vote.context_id = vc.id
>                                         AND thing_id = 1),
> facebook_facebookprofile
> WHERE mydatabase_vote.user_id = facebook_facebookprofile.user_id
> AND facebook_facebookprofile.gender = 'male'
> GROUP BY thing_id, name
> ORDER BY md.id
>
> could be the solution. But the "filter" by gender comes after the LEFT
> JOIN and is messing everything up. It will cut off a vote_context if
> no vote for that context exists (the original, but solved, problem)

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.