Postgres query - add column

Hi I have a query that gets 2 different counts

select (select count(ENDED_STATUS) as OK
          from RUNINFO_HISTORY where ENDED_STATUS=16
           and START_TIME >= to_date('20150301070000','YYYYMMDDHH24MISS')
           and END_TIME <= to_date('20150331065959','YYYYMMDDHH24MISS')) as "OK",
       (select count(ENDED_STATUS) as NOTOK
          from RUNINFO_HISTORY
         where ENDED_STATUS=32
           and START_TIME >= to_date('20150301070000','YYYYMMDDHH24MISS')
           and END_TIME <= to_date('20150331065959','YYYYMMDDHH24MISS')) as "NOTOK"


Result is


OK              NOTOK
----------       ------------
34               5



I need to add a column to the front of the query but not sure how to do it.

The column is "application"

So I need it to look like the following

Application          OK               NOTOK
---------------         ------------      --------------
app1                   25                 3
app2                   35                 10



Not sure were in the query to add the "application"

Thanks,

Mike
LVL 1
mikeysmailbox1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbbishopCommented:
Try:
select Application, (count(ENDED_STATUS) as OK
           from RUNINFO_HISTORY ih where ih.Application = oh.Application
            and ENDED_STATUS=16
            and START_TIME >= to_date('20150301070000','YYYYMMDDHH24MISS')
            and END_TIME <= to_date('20150331065959','YYYYMMDDHH24MISS')) as "OK",
        (select count(ENDED_STATUS) as NOTOK
           from RUNINFO_HISTORY ih
          where ih.Application = oh.Application
            and ENDED_STATUS=32
            and START_TIME >= to_date('20150301070000','YYYYMMDDHH24MISS')
            and END_TIME <= to_date('20150331065959','YYYYMMDDHH24MISS')) as "NOTOK"
from RUNINFO_HISTORY oh
group by Applicaiton
0
dbbishopCommented:
Above is not most efficient if dealing with lots of data (e,g. millions of rows). Will be best if Application is indexed. I am a SQL Server developer, but doing some quick searching, I found the LATERAL statement is analogous to CROSS APPLY in SQL Server. Not sure how the optimizer works in Postgres, but it would probably resolve the same. The main difference is that using LATERAL/CROSS APPLY usually results in cleaner code.
0
awking00Commented:
It's difficult to confirm what you're trying to accomplish with the sample data you show. I would have suspected that OK counts of 34 and NOTOK counts of 5, the counts for app1 and app2 would combine to those. Perhaps you can provide some sample data and your expected output for the relevant columns (no need for all of them) in your runinfo_history table.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

dbbishopCommented:
Correction:
SELECT      Application,
            (
              SELECT      COUNT(ENDED_STATUS) AS OK
              FROM            RUNINFO_HISTORY ih
              WHERE            ih.Application = oh.Application
                              AND ENDED_STATUS = 16
                              AND START_TIME >= to_date('20150301070000','YYYYMMDDHH24MISS')
                              AND END_TIME <= to_date('20150331065959','YYYYMMDDHH24MISS')
             ) AS "OK",
            (
              SELECT      COUNT(ENDED_STATUS) AS NOTOK
              FROM            RUNINFO_HISTORY ih
              WHERE            ih.Application = oh.Application
                              AND ENDED_STATUS = 32
                              AND START_TIME >= to_date('20150301070000','YYYYMMDDHH24MISS')
                              AND END_TIME <= to_date('20150331065959','YYYYMMDDHH24MISS')
             ) AS "NOTOK"
FROM      RUNINFO_HISTORY oh
GROUP BY Application
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mikeysmailbox1Author Commented:
This worked perfectly.

Thanks
0
awking00Commented:
I think using a common table expression might have worked as well
WITH CTE AS
(SELECT Application, ENDED_STATUS
 FROM RUNINFO_HISTORY ih
 WHERE START_TIME >= to_date('20150301070000','YYYYMMDDHH24MISS')
   AND END_TIME <= to_date('20150331065959','YYYYMMDDHH24MISS')
)
select application,
sum(case when ended_status = 16 then 1 else 0 end) OK,
sum(case when ended_status = 32 then 1 else 0 end) NOTOK
from cte
where ended_status in (16,32)
group by application
order by application;
0
mikeysmailbox1Author Commented:
Hi just figured out the above code is not working.

SELECT      Application,
            (
              SELECT      COUNT(ENDED_STATUS) AS OK
              FROM            RUNINFO_HISTORY ih
              WHERE            ih.Application = oh.Application
                              AND ENDED_STATUS = 16
                              AND START_TIME >= to_date('20150301070000','YYYYMMDDHH24MISS')
                              AND END_TIME <= to_date('20150331065959','YYYYMMDDHH24MISS')
             ) AS "OK",
            (
              SELECT      COUNT(ENDED_STATUS) AS NOTOK
              FROM            RUNINFO_HISTORY ih
              WHERE            ih.Application = oh.Application
                              AND ENDED_STATUS = 32
                              AND START_TIME >= to_date('20150301070000','YYYYMMDDHH24MISS')
                              AND END_TIME <= to_date('20150331065959','YYYYMMDDHH24MISS')
             ) AS "NOTOK"
FROM      RUNINFO_HISTORY oh
GROUP BY Application




for some reason the query is not getting the data for the column OK.

application     ok     notok    
 --------------  -----  --------
 OS_PATCHING     0      0        
 TEST_PAT        0      0        
 (null)          0      0



but if I run the SQL as

select application, ended_status  from runinfo_history
where ended_status=16 and start_time >= to_date('20150512070000','yyyymmddhh24miss')
and end_time <= to_date('20150513065959','yyyymmddhh24miss')

I get the entry.

 application     ended_status    
 --------------  ---------------
 (null)          16  

Not sure why I get one on the individual on and on the combined I don't get one.

Thanks
0
awking00Commented:
>>Not sure why I get one on the individual on and on the combined I don't get one.<<
Can you provide some sample data (application, end_status, start_time, and end_time) and the desired output for the query that is not getting the results you expect?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.