• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

SQL 2005 Group By Query

The below SQL 2005 Group by query works.  The additional solution I am looking for is when a Null appears for the ProName.  I would like a solution that places the word: "unknown" when the group by results in a Null for ProName?   Currently it lists the ProNames and sometimes a Null when the provider is missing from the tables.

SELECT zReports.PhysResp, sum(1) as Volume,
Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle as ProName
FROM Orders
INNER JOIN zReports ON Orders.VisitID = zReports.VisitID
left JOIN Provider ON Provider.ProID = zReports.PhysResp  
WHERE
(zReports.TimeIn2 >= '200701010000' AND zReports.TimeIn2 <= '201108302359' and zReports.FacNo = 1 and Orders.OrderType = 'RAD')
Group by ZReports.PhysResp,Provider.ProLName, Provider.ProFName, Provider.Protitle
Order by Provider.ProLName, Provider.ProFName, Provider.Protitle
0
dastaub
Asked:
dastaub
2 Solutions
 
tangchunfengCommented:
SELECT 'Number of Titles', Count(*)
FROM titles
GROUP BY
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 and 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END
GO
0
 
Kevin CrossChief Technology OfficerCommented:
It looks like you just need to wrap ProName expression with COALESCE():
COALESCE(Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle, 'Unknown') as ProName

You may also want to account for NULL in any one of the columns also.
0
 
Kevin CrossChief Technology OfficerCommented:
COALESCE(Provider.ProLName + COALESCE(' ' + Provider.ProFName, '') + COALESCE(', ' + Provider.Protitle, ''), 'Unknown') as ProName
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
HainKurtSr. System AnalystCommented:
you can use isNull(exp, ret_if_null) or case & IsNull combination

IsNull(Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle, 'Unknown') as ProName

or

case
when Provider.ProLName is null or Provider.ProFName is null or Provider.Protitle is null then 'Unknown'
else Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle
end as ProName

or

case
when Provider.ProLName is null and Provider.ProFName is null and Provider.Protitle is null then 'Unknown'
else IsNull(Provider.ProLName,'') + ' ' + IsNull(Provider.ProFName,'') + ', ' + IsNull(Provider.Protitle,'')
end as ProName
0
 
csenseCommented:
SELECT            zReports.PhysResp,
                  sum(1) as Volume,
                  CASE WHEN ISNULL(Provider.ProID, '') = '' THEN 'unknown'
                        ELSE Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle
                        END as ProName
FROM
                  Orders INNER JOIN
                  zReports ON Orders.VisitID = zReports.VisitID left JOIN      
                  Provider ON Provider.ProID = zReports.PhysResp  
WHERE
                  (zReports.TimeIn2 >= '200701010000'
                  AND zReports.TimeIn2 <= '201108302359'
                  and zReports.FacNo = 1
                  and Orders.OrderType = 'RAD')
Group by
                  ZReports.PhysResp,
                  Provider.ProLName,
                  Provider.ProFName,
                  Provider.Protitle
Order by
                  Provider.ProLName,
                  Provider.ProFName,
                  Provider.Protitle
0
 
csenseCommented:
might need to switch Provider.ProID = zReports.PhysResp   around.

SELECT		zReports.PhysResp, 
			sum(1) as Volume,
			CASE WHEN ISNULL(Provider.ProID, '') = '' THEN 'unknown'
				ELSE Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle
				END as ProName
FROM 
			Orders INNER JOIN 
			zReports ON Orders.VisitID = zReports.VisitID left JOIN	
			Provider ON zReports.PhysResp = Provider.ProID
WHERE 
			(zReports.TimeIn2 >= '200701010000' 
			AND zReports.TimeIn2 <= '201108302359' 
			and zReports.FacNo = 1 
			and Orders.OrderType = 'RAD')
Group by 
			ZReports.PhysResp,
			Provider.ProLName, 
			Provider.ProFName, 
			Provider.Protitle 
Order by 
			Provider.ProLName, 
			Provider.ProFName, 
			Provider.Protitle

Open in new window

0
 
GhunaimaCommented:
Try this
SELECT zReports.PhysResp, sum(1) as Volume,
isnull(Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle, 'Unknown') as ProName
FROM Orders 
INNER JOIN zReports ON Orders.VisitID = zReports.VisitID
left JOIN Provider ON Provider.ProID = zReports.PhysResp  
WHERE 
(zReports.TimeIn2 >= '200701010000' AND zReports.TimeIn2 <= '201108302359' and zReports.FacNo = 1 and Orders.OrderType = 'RAD')
Group by ZReports.PhysResp,Provider.ProLName, Provider.ProFName, Provider.Protitle 
Order by Provider.ProLName, Provider.ProFName, Provider.Protitle

Open in new window

0
 
dastaubAuthor Commented:
all answer were thoughtful.  Below is solution:

SELECT zReports.PhysResp, sum(1) as Volume,
IsNull(Provider.ProLName + ' ' + Provider.ProFName + ', ' + Provider.Protitle, 'Unknown') as ProName
FROM Orders
INNER JOIN zReports ON Orders.VisitID = zReports.VisitID
left JOIN Provider ON Provider.ProID = zReports.PhysResp  
WHERE
(zReports.TimeIn2 >= '200701010000' AND zReports.TimeIn2 <= '201108302359' and zReports.FacNo = 1 and Orders.OrderType = 'RAD')
Group by ZReports.PhysResp,Provider.ProLName, Provider.ProFName, Provider.Protitle
Order by Provider.ProLName, Provider.ProFName, Provider.Protitle
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

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