Solved

Some quick SQL help

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 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

18 Experts available now in Live!

Get 1:1 Help Now