Home » SQL & PL/SQL » SQL & PL/SQL » help in the count (Oracle, 8.1.7, Windows Server 2003)
help in the count [message #377306] Mon, 22 December 2008 05:13 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi Experts,

I have a sql like this

SELECT DISTINCT SUBSTR (CHS_NO, 4, 6) chs_no, MAX (cur_mileage) mileage
FROM mss_ro_hdr
WHERE SUBSTR (chs_no, 4, 6) IN ('CYH52S')
AND CUR_MILEAGE BETWEEN 25000 AND 50000
GROUP BY CHS_NO;

My output is

CHS_NO MILEAGE
CYH52S 25450
CYH52S 26273
CYH52S 26289
CYH52S 26511
CYH52S 26709
CYH52S 26995
CYH52S 27150

but I want the output like this

CHS_NO COUNT
CYH52S 7

Can anyone help me?
Re: help in the count [message #377307 is a reply to message #377306] Mon, 22 December 2008 05:17 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
use

GROUP BY SUBSTR (CHS_NO, 4, 6);


Thanks
Trivendra
Re: help in the count [message #377311 is a reply to message #377307] Mon, 22 December 2008 05:33 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
and don't use MAX if you are looking for the count.
Previous Topic: UPDATE is really a DELETE followed by an INSERT
Next Topic: Query help with cumulative sum to be broken into sections
Goto Forum:
  


Current Time: Sun Dec 11 02:06:34 CST 2016

Total time taken to generate the page: 0.08121 seconds