SQL Query if Exists column concatenate results

Hi Guys,

I have attached a spreadsheet illustrating what I am trying to achieve with the desired query result. I know how to check if a record has rows in multiple tables and return the different row counts from the tables a rows using.

SELECT        COUNT(*) AS Count1
FROM            Table1
UNION ALL
SELECT        COUNT(*) AS Count2
FROM            Table2
UNION ALL
SELECT        COUNT(*) AS Count3
FROM            Table3
UNION ALL
SELECT        COUNT(*) AS Count4
FROM            Table4
UNION ALL
SELECT        COUNT(*) AS Count5
FROM            Table5

Open in new window


Could someone please help me right a query to produce my desired result as per my spreadsheet.

Many thanks in advance
databarracksAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this would look like something like this (starter)
select c.customer_name
  , ( select count(*) from tblService1 s where s.customer_ID = c.customer_ID and s.active = 'Yes' ) service_1
  , ( select count(*) from tblService2 s where s.customer_ID = c.customer_ID and s.active = 'Yes' ) service_2
  , ( select count(*) from tblService3 s where s.customer_ID = c.customer_ID and s.active = 'Yes' ) service_3
  , ( select count(*) from tblService4 s where s.customer_ID = c.customer_ID and s.active = 'Yes' ) service_4
  , ( select count(*) from tblService5 s where s.customer_ID = c.customer_ID and s.active = 'Yes' ) service_5
  from tblCustomers c

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try again to attach the spreadsheet...
0
 
databarracksAuthor Commented:
Ok second attempt
Query-Plan.xlsx
0
 
databarracksAuthor Commented:
Hi Guy,

That did the trick,many thanks again for your help
0
 
databarracksAuthor Commented:
Very good answer and quick response
0
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.

All Courses

From novice to tech pro — start learning today.