Se connecter
 
AccueilLibrairieFormationTéléchargementsSupport techniqueCommunautésForums
Pour les professionnels de l’informatique >
> 
> 
SQL server, how to include group by item in where clause

Answered SQL server, how to include group by item in where clause

  • dimanche 27 mai 2012 09:19
     
     
    Connectez-vous pour voter
    0

    Hi All,

    I have a query like

    SELECT ModelNo 'Model No' , sectiondesc 'Complaint Section' ,
    COUNT(CASE WHEN Status='Open' THEN status END)'New Calls', COUNT(CASE
    WHEN Status <> 'Open' and Status <> 'Closed' and Status <> 'Cancel'
    THEN status END)'Pending Calls', COUNT(CASE WHEN Status='Closed' THEN
    status END)'Closed Calls' from complaints Where modifiedon
    between '2012-05-01' and '2012-05-10' group by
    sectiondesc, modelno order by modelno

    This is to display count of New Calls, Pending Calls and Closed calls against each Models within a selected period. Now, I need to include one more condition like the selected count should > =10. Means display all New Calls, Pending Calls and closed calls against each models within a certain period and count >=10

    If anyone can help me on this, would be great

    Thanks n Regards

    A.Salam

     

Toutes les réponses

  • dimanche 27 mai 2012 09:30
     
     
    Connectez-vous pour voter
    0

    Hi,

    To display Records greater than 10 use the below Query

    SELECT ModelNo 'Model No' , sectiondesc 'Complaint Section' ,
    COUNT(CASE WHEN Status='Open' THEN status END)'New Calls', COUNT(CASE
    WHEN Status <> 'Open' and Status <> 'Closed' and Status <> 'Cancel'
    THEN status END)'Pending Calls', COUNT(CASE WHEN Status='Closed' THEN
    status END)'Closed Calls' from complaints Where modifiedon
    between '2012-05-01' and '2012-05-10' group by
    sectiondesc, modelno HAVING COUNT(CASE WHEN Status='Open' THEN status END)>=10 AND

    COUNT(CASEWHEN Status <> 'Open' and Status <> 'Closed' and Status <> 'Cancel'
    THEN status END)>=10 AND

     COUNT(CASE WHEN Status='Closed' THEN status END)>=10  order by modelno

    PS.Shakeer Hussain

     
  • lundi 28 mai 2012 00:28
     
     Traitée A du code
    Connectez-vous pour voter
    0

    Try below.

    select ModelNo 'Model No'
,sectiondesc 'Complaint Section'
,NewCalls 'New Calls'
,PendingCalls 'Pending Calls'
,ClosedCalls 'Closed Calls'
from
(
SELECT ModelNo 
,sectiondesc  
,COUNT(CASE WHEN Status='Open' THEN status END)'NewCalls'
,COUNT(CASE WHEN Status <> 'Open' and Status <> 'Closed' and Status <> 'Cancel' THEN status END)'PendingCalls'
, COUNT(CASE WHEN Status='Closed' THEN status END) 'ClosedCalls' 
from complaints Where 
modifiedon between '2012-05-01' and '2012-05-10'
group by sectiondesc, modelno 
)
where NewCalls >= 10 and PendingCalls >= 10 and ClosedCalls >= 10
order by modelno

    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you


     
  • jeudi 31 mai 2012 18:14
     
     
    Connectez-vous pour voter
    0

    Hello Eshani.....

      Thanks working .....Thanks a lot

    Warm Regards

    A.Salam

     
  • jeudi 31 mai 2012 18:16
     
     
    Connectez-vous pour voter
    0

    Thanks Sayed......Got another solution. Thanks a lot

    Warm Regards

    A.Salam