SQL Order problem

Stevil

hey all,

I'm doing some repairs on some sites for work and the last coder left a query in a terrible state for me to work with, alas I need to repair it still 🙁

anyways my problem is I need to order by a COUNT() but have a where clause in that count. So something along the lines of

ORDER BY (COUNT(q.quote_id) WHERE q.status < 4)

but this doesn't work, if anyone could offer any help I'd be grateful. I'm suer its possible I'm just nor sure how .

olaf

and if you a statement like this:

SELECT * FROM table WHERE q.status < 4 ORDER BY COUNT(q.quote_id)

Stevil

Sorry, I should have explained better. Was a bit rushed while I was in the office. I'll take the time to explain it now.

ORDER BY (COUNT(q.quote_id) WHERE q.status < 4)

is what I'm looking for

q.status is the status of a quote, if it be open, paid, shipped an so on. If the quote status is less then 4 its a "None Processed" quote.

This quotes table is linked to the members and INNER JOINED in this query (unfortuantly company wants database structure non public so can't post whole thing 🙁)

The basic ID though is

SELECT count(q.quote_id) FROM q.quotes INNER JOIN m.members ON q.member_id = m.member_id GROUP BY q.quote_id ORDER BY (COUNT(q.quote_id) WHERE q.status < 4)

so Olaf even though technically what you said is correct going off what I originally posted it will not work with this as I need to count total quotes but order by quotes which are not processed. Thank you anyways

olaf

i'm really sure that this will give the right output:

SELECT count(q.quote_id) FROM q.quotes INNER JOIN m.members ON q.member_id = m.member_id WHERE q.status < 4vGROUP BY q.quote_id ORDER BY COUNT(q.quote_id)

Stevil

The problem with this code is that in the first part "SELECT count(q.quote_id)" will still only do <4 while I need to count it ALL records. This is why I have to do it in the "ORDER" statement not the "WHERE" conditions.

Placing the condition in the WHERE condition will take effect over the entire statement meaning that all the calculations will only include records where "WHERE q.status < 4" I wish it was just that simple though 🙁

olaf

OK,
i noticed somthing with an if statement inside the sql . Take a look in the manual (for if).

Stevil

There are 2 forms of if statements, one of which is for command line, this is similar to the normal if else construct. This is not a great deal of help since I'm not using command line.

The second I haven't ACTUALLY used before but the impression I get is that its similar to the "?" opperator. if(condition, true return, false return). On the off chance ordering by an if statement might work I gave it a shot an unf'ortunatly it didn't (When I confirmed the IF statement was pretty logial wouldn't) Thanks for the idea but yet again another dead end 🙁