Solved

Some quick SQL help

Posted on 2007-12-06
4
196 Views
Last Modified: 2010-03-20
This SQL statement:

"SELECT DISTINCT  HGR6.CALL_REQ.REF_NUM, HGR6.CALL_REQ.STATUS, to_date('31/12/1969 23:59:59','DD/MM/YYYY HH24:MI:SS')+(HGR6.CALL_REQ.OPEN_DATE/(24*60*60)) OPENDATE, HGR6.CTCT.C_LAST_NAME, HGR6.CTCT.C_FIRST_NAME FROM HGR6.CALL_REQ LEFT JOIN HGR6.CTCT ON HGR6.CALL_REQ.ASSIGNEE=HGR6.CTCT.ID WHERE HGR6.CALL_REQ.GROUP_ID='49540961' OR HGR6.CALL_REQ.GROUP_ID= '78476563' OR HGR6.CALL_REQ.GROUP_ID = '146196489' OR HGR6.CALL_REQ.GROUP_ID ='51898172' AND HGR6.CALL_REQ.STATUS <> 'CL' AND HGR6.CALL_REQ.PARENT IS NULL ORDER BY HGR6.CTCT.C_LAST_NAME"

produces the results with the following columns:

REF_NUM              STATUS             OPENDATE            C_LAST_NAME           C_FIRST_NAME
12345                     Open                   21/10/2007            Smith                             John
11111                     Open                   11/11/2007            doe                                 Jane
82828                     Open                   15/10/2007             Smith                             John
88388                     Open                   07/120/2007           Green                             Michael

in a big long list.
How can I change the SQL so it will simply count the number of results for each person returned
So it would be like

C_LAST_NAME           C_FIRST_NAME               Count
Smith                                        John                        2
doe                                             Jane                      1
Green                                        Michael                   1

Thanks!
0
Comment
Question by:sterankin
  • 2
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
Ashish Patel earned 150 total points
ID: 20418990
Try this

SELECT HGR6.CTCT.C_LAST_NAME, HGR6.CTCT.C_FIRST_NAME, Count(HGR6.CTCT.C_LAST_NAME)
FROM HGR6.CALL_REQ
LEFT JOIN HGR6.CTCT
      ON HGR6.CALL_REQ.ASSIGNEE=HGR6.CTCT.ID
      WHERE HGR6.CALL_REQ.GROUP_ID='49540961' OR HGR6.CALL_REQ.GROUP_ID= '78476563' OR HGR6.CALL_REQ.GROUP_ID = '146196489' OR HGR6.CALL_REQ.GROUP_ID ='51898172'
      AND HGR6.CALL_REQ.STATUS <> 'CL' AND HGR6.CALL_REQ.PARENT IS NULL
Group By HGR6.CTCT.C_LAST_NAME, HGR6.CTCT.C_FIRST_NAME
ORDER BY HGR6.CTCT.C_LAST_NAME
0
 

Author Comment

by:sterankin
ID: 20420207

thanks that worked!
I have an additional question so I have increased the points - what is wrong with this SQL, I am getting "not a group function" error:

closedSql = "SELECT DISTINCT  HGR6.CALL_REQ.REF_NUM, HGR6.CALL_REQ.STATUS, to_date('31/12/1969 23:59:59','DD/MM/YYYY HH24:MI:SS')+(HGR6.CALL_REQ.OPEN_DATE/(24*60*60)) OPENDATE, to_date('31/12/1969 23:59:59','DD/MM/YYYY HH24:MI:SS')+(HGR6.CALL_REQ.CLOSE_DATE/(24*60*60)) CLOSEDATE,Count(HGR6.CTCT.C_LAST_NAME), HGR6.CTCT.C_FIRST_NAME FROM HGR6.CALL_REQ LEFT JOIN HGR6.CTCT ON HGR6.CALL_REQ.ASSIGNEE=HGR6.CTCT.ID WHERE HGR6.CALL_REQ.GROUP_ID='" + teamID + "'  AND (HGR6.CALL_REQ.CLOSE_DATE >= '" + startSeconds.TotalSeconds+"') AND (HGR6.CALL_REQ.CLOSE_DATE <= '"+endSeconds.TotalSeconds+"')AND HGR6.CALL_REQ.STATUS = 'CL' AND HGR6.CALL_REQ.PARENT IS NULL ORDER BY HGR6.CTCT.C_LAST_NAME";

will assign points after this
0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20420424
As this qurey is not a group by query you cannot use Count(HGR6.CTCT.C_LAST_NAME), ie you cannot use count, sum, avg ...or any aggregated functions.
0
 

Author Comment

by:sterankin
ID: 20420465
Many Thanks - I missed that part!
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question