Forum Stats

  • 3,783,314 Users
  • 2,254,756 Discussions
  • 7,880,359 Comments

Discussions

group by clause

643308
643308 Member Posts: 34
edited Jun 11, 2009 9:52AM in SQL & PL/SQL
hi,
I have a requirement as below
I have a query:

SELECT
TO_CHAR(DTE,'YYYY-MM') AS MONTH, COUNT(id) AS Items
FROM PA
WHERE
GROUP BY
TO_CHAR(DTE,'YYYY-MM')
It generates me some output like
MONTH Items
-------------------------
2008-12 1215
...

But now the new requirement is to divide this id (varchar) on basis of starting with numeric and starting with alphabets .
for numeric count I have query as below which works fine and similarly for id starting with character also it works fine
SELECT
TO_CHAR(DTE,'YYYY-MM') AS MONTH, COUNT(id) AS Items
FROM PA
WHERE REGEXP_LIKE (id, '^\[0-9\]+')
GROUP BY
TO_CHAR(DTE,'YYYY-MM')

SELECT
TO_CHAR(DTE,'YYYY-MM') AS MONTH, COUNT(id) AS Items
FROM PA
WHERE REGEXP_LIKE (id, '^\[a-z\]+')
GROUP BY
TO_CHAR(DTE,'YYYY-MM')

But I want final out put as such below:
MONTH Items startinwithnumber startinwithalpha
----------------------------------------------------------------------------------------------------
2008-12 1215 215 1000
...


Can anyone help on this.
Thanks in advance.

Edited by: user640305 on Jun 11, 2009 6:49 AM
Tagged:

Answers

  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    edited Jun 11, 2009 9:52AM
    Try something like this:
    SELECT	TO_CHAR(DTE,'YYYY-MM') AS MONTH
    , 	COUNT(
    		CASE WHEN REGEXP_LIKE (stud_id, '^[0-9]+') 
    		THEN id 
    		ELSE NULL 
    		END
    	) AS startinwithnumber
    , 	COUNT(
    		CASE WHEN REGEXP_LIKE (stud_id, '^[a-z]+')
    		THEN id 
    		ELSE NULL 
    		END
    	) AS startinwithalpha
    FROM PA
    GROUP BY
    TO_CHAR(DTE,'YYYY-MM')
    When you would like us to write a query for you it is best to always:

    1. Post version number
    2. Post sample data (e.g. DDL/DML)
    3. Post expected results
    4. Use { code } tags (without spaces)
    Centinul
This discussion has been closed.