Solved

Some quick SQL help

Posted on 2007-12-06
4
195 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now