SQL Group By help.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Group By help....

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-11-04 : 11:16:24

The below query displays a list of companies grouped by there name.
The companies that are grouped are only related by name, they could be a branch or home office. I am trying to display address information for each instance of company name(fields: add1, add2, city, state, zip).
I do not think a subquery will solve it since there are multiple address records for each company. Hope this makes sense, below is my incomplete query and the results which are generated.

Select count(Cust.CompanyName) 'Number of Times Company is Present', Cust.CompanyName as 'Company Name'
-- need to add address information for each instance of company. for companies with an instance of >=2,
-- the address will be different even though the company name is the same.
from CustomerService CustSer, Customer c1, Address Addr, Services Svc, Customer cust
where CustSer.CoID = Cust.CoID
and CustSer.AddressID = Addr.AddressID
and CustSer.ServiceID = Svc.ServiceID
and c1.coid = CustSer.BillingHomeOfficeCoID
and CustSer.billinghomebranchind = 'H'
and (Svc.ServiceDescription = 'Checking' or Svc.ServiceDescription = 'DIRS' or Svc.ServiceDescription = 'ALPS')
GROUP BY Cust.CompanyName
order by Cust.CompanyName

2 5 STAR LIFE INS. CO.
2 AAA LIFE INS. CO.
2 ACA ASSURANCE
1 ACACIA MUTUAL LIFE INS. CO.
1 ACADIA LIFE
1 ACE LIFE INSURANCE COMPANY
2 AGL LIFE ASSUR. CO.
2 AIG LIFE INS. CO.
2 AIG LIFE INS. CO. OF CANADA
1 ALFA LIFE INS. CORP.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-04 : 11:22:55

select (select count(*) from Customer where COID = CustSer.COID)as cnt, address1, address2, city, state,zip
from CustomerService CustSer, Customer c1, Address Addr, Services Svc
where
CustSer.AddressID = Addr.AddressID
and CustSer.ServiceID = Svc.ServiceID
and c1.coid = CustSer.BillingHomeOfficeCoID
and CustSer.billinghomebranchind = 'H'
and (Svc.ServiceDescription in ('Checking' ,'DIRS','ALPS')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 11:38:57

[code]SELECT svc.CompanyName,
MAX(LEFT(al.AddrList,LEN(al.AddrList)-1)) AS AddrList,
....
from CustomerService CustSer
JOIN Customer c1
ON c1.coid = CustSer.BillingHomeOfficeCoID
JOIN Services Svc
ON CustSer.ServiceID = Svc.ServiceID
JOIN Customer cust
ON CustSer.CoID = Cust.CoID
CROSS APPLY (SELECT AddressField + ','
FROM Address
WHERE AddressID= CustSer.AddressID
FOR XML PATH(''))al(AddrList)
where CustSer.billinghomebranchind = 'H'
and (Svc.ServiceDescription = 'Checking' or Svc.ServiceDescription = 'DIRS' or Svc.ServiceDescription = 'ALPS')
GROUP BY Cust.CompanyName
order by Cust.CompanyName[/code]

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-11-04 : 11:54:32

Thanks Visakh16, I substitued 'AddressField' with 'line1' as shown below.

SELECT svc.CompanyName,
MAX(LEFT(al.AddrList,LEN(al.AddrList)-1)) AS AddrList
from CustomerService CustSer
JOIN Customer c1
ON c1.CoID = CustSer.BillingHomeOfficeCoID
JOIN Services Svc
ON CustSer.ServiceID = Svc.ServiceID
JOIN Customer cust
ON CustSer.CoID = Cust.CoID
CROSS APPLY (SELECT line1 + ','
FROM Address
WHERE AddressID= CustSer.AddressID
FOR XML PATH(''))al(AddrList)
where CustSer.billinghomebranchind = 'H'
and (Svc.ServiceDescription = 'Checking' or Svc.ServiceDescription = 'DIRS' or Svc.ServiceDescription = 'ALPS')
GROUP BY Cust.CompanyName
order by Cust.CompanyName

When run, I get the following:

Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'APPLY'.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'FOR'.

I do not see what is wrong syntax wise... Not familiar with APPLY and XML PATH, going to do a little research.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 11:59:05

Are you using sql 2005 with compatibility level 90?

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-11-04 : 12:05:17

We are using SQL 2005 with a compatibility level of 'SQL Server 2000 (80)'. I do not see an option for 90.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 12:07:06