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

Recommended Answers

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 …

Jump to Post

All 2 Replies

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

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 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.