Grouping and sums (need help)

[Floydian]

Hey folks, I got a lil problem that is stumping me.

 

There are two tables. One is for lawyers that players own. The other is for lawyer types.

For brevity, I'll list the needed tables, and column names.

 

• Table: my_lawyers
  
  • id --- auto incrementing primary key
    
  • userid --- who the lawyer belongs to
    
  • lawyer_id --- what kind of lawyer this is

   

  [*]lawyer_types

  • id --- auto incrementing primary key
    
  • salery --- what is this lawyers weekly salery?

   

 

What I need is to find out how much each player will owe in lawyer salaries for the week. There is no quantity field in the my_lawyers table. Players could have 4 of the first type of lawyer, 3 of the second, and 2 of the third type.

suppose the first type lawyer is paid 100 per week, the second 200, and the third 300, the query should do this

lawyer type 1: 4 * 100 +

lawyer type 2: 3 * 200 +

lawyer type 3: 2 * 300

which would total 1600

The result of the query should provide one row, for every player that owns a lawyer. And this row should represent the total salery of all of that player's lawyers, and it should return that player's userid as well.

If anyone can hit that up for me, that'd be great.

I've come up with a few different queries, but all of them proved to return the wrong information.

Thanks in advance to anyone that tries to figure this out :D

[Floydian]

Re: Grouping and sums (need help)

 

select userid, sum(total_per_type) as total from (
select team1.userid, (
 select count(*) * (select law1.salery from lawyer_types as law1 where law1.id = team1.lawyer_id)
 from my_lawyers as team2 where team2.userid = team1.userid and team2.lawyer_id = team1.lawyer_id)
 as total_per_type
from my_lawyers as team1 group by team1.userid, team1.lawyer_id) as subquery group by userid

 

Well, it's fugly, but it works. :mrgreen:

 

Anyone think they can do better?

[Guest Anonymous]

Re: Grouping and sums (need help)

Hmm, not sure, but I guess you have one row per lawyer per user in the my_lawyers table so:

SELECT

l.userid AS UserID,

SUM(lt.salery) AS PaymentRequired

FROM

my_lawyers l

LEFT JOIN

lawyer_types lt ON (lt.id = l.lawyer_id)

GROUP BY

l.userid

[Floydian]

Re: Grouping and sums (need help)

Yup, that works good :D

I regard my query as a work of art, and hate to see it go.

Thanks a bunch Nyna :D

[Guest Anonymous]

Re: Grouping and sums (need help)

Most welcome as always...

I think you should keep your query though ... It's a marvelous demonstration of finding the most complex solution to a simple problem ;)

[Floydian]

Re: Grouping and sums (need help)

It should be commissioned into a work of art of some kind. Perhaps a gold placard.

Ya know what I kept missing, was the join. Normally joining a table is something I do with no thought, but the addition of a group by clause threw me off. So I kept trying to do a query like yours, but with a subquery for the sum, and that wasn't working out.

So I crafted the query I ended up with by starting from the subquery, and working my way up to the outer query lol.

I will say this, it opened a whole new thing for me. Subqueries in the from clause for making a custom table on the fly. :D

But alas, you query most definitely runs faster than mine and the two queries will be swapped out. lol

[Guest Anonymous]

Re: Grouping and sums (need help)

Glad you've seen the light ;)

subqueries can be useful, but in a lot of cases they can be re-written as joins but then you really have to start using EXPLAIN or a decent query optimizer to check all the correct indices are in place in order to limit (or pref. get rid off) full row-scans.

I think we should have a "Fugliest SQL" award ... Assuming we did, I will certainly vote yours as *the* fugliest so far ;)

[Floydian]

Re: Grouping and sums (need help)

Beauty is in the eye of the beholder my dear ;)

BTW, I've got some other ones that are pretty ridiculous looking. lol

Normally I go with a join. In fact, I'm more used to them. Subqueries are really kinda new to me. lol

Anyways, I will gladly accept the infamy of being the owner of the fugliest query award!