Improve company productivity with a Business Account.Sign Up

x
?
Solved

Some quick SQL help

Posted on 2007-12-06
4
Medium Priority
?
205 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 600 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 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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.

Join & Write a Comment

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.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Did you know PowerShell can save you time with SaaS platforms? Simply leverage RESTfulAPIs to build your own PowerShell modules. These will kill repetitive tickets and tabs, using the command Invoke-RestMethod. Tune into this webinar to learn how…

580 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