Member Avatar for bobby08

Hi,
I wish I could solve this on my own but I tried searching and googling but nothing doing.


Althought worse comes to worse, I can use Perl to do what I want, but I would prefer having a SQL syntax that can handle it.

Problem:
3 columns: username, daily units, rare units.
I need to add daily units + rare units , group them by username, and then order it by the sum of daily units and rare units.

So:

username | daily units | rare units
bobby1 ........ 100 ........ 200
bobby2 ........ 200 ........ 300
bobby3 ........ 300 ........ 400


Table results should be:
username | total units
bobby3 ........ 700
bobby2 ........ 500
bobby1 ........ 300

I'm sure there is a way to do it but I can't seem to figure it out.

I've tried
SELECT username, SUM(daily_units, rare_units) FROM table GROUP BY username ORDER BY SUM(daily_units, rare_units)

^ gave error obv.

I also tried other lil syntax, but keep getting no results!!

I can have Perl store this table inside a hash, and figure out the rankings that way, but SQL syntax would avoid all the extra perl coding needed.

Thanks for taking your time to read this post.

Cheers,
Bobby

  • 3 Contributors
  • 2 Replies
  • 711 Views
  • 7 Months Discussion Span
  • Latest Post Latest Post by fasttoshiba
Member Avatar for MCP

try:

SELECT username, SUM(daily_units+rare_units) FROM table GROUP BY username ORDER BY SUM(daily_units+rare_units) desc

the sum function works by summing rows, not separate fields (i.e. it takes a single argument, an expression to be summed over rows). That expression in your case is a sum itself, but it could be anything that involves fields of the row

i also added 'desc' to the end of the order by, so that you get top unit holders first, as in your example

Member Avatar for fasttoshiba

hi.....,
But how do u do that for multi select statements


My code:


select session,
(select count (*)
from OH r
join h_prod prod on r.prod_id = prod.id
where r.conf_id = c.id
and person_type = 's'
and gender = 'm'
and prod.name= 'double room') as "SMdouble",

(select count(*)
from OH r
join h_prod prod on r.prod_id = prod.id
where r.conf_id=c.id
and person_type = 's'
and gender = 'f'
and prod.name ='double room')as "SFDouble",

from conference c
order by session


now i wnaa add SMDouble and SFDouble......

how do u do that

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.