[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Some quick SQL help

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
sterankin
Asked:
sterankin
  • 2
  • 2
1 Solution
 
Ashish PatelCommented:
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
 
sterankinAuthor Commented:

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
 
Ashish PatelCommented:
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
 
sterankinAuthor Commented:
Many Thanks - I missed that part!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now