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.

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.


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.


10 Years
Discussion Span
Last Post by fasttoshiba


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


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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.