How to create a Table from multiple queries

Question

I am trying to create a table counting items from two different tables to get totals and subtotals as below:

(select count(*) from ccustomer AS TotalCustomers)    
(select count(*) from ccustomer where floating = 0 AS ActiveCustomers),    
(select count(*) from ccustomer where floating = 1 AS FloatingCustomers),    
(select count(*) from pproperty AS TotalProperties)    
(select count(*) from pproperty where occcustno = 0 and propstat <> 'de' AS VoidProperties),    
(select count(*) from pproperty where occcustno = 0 and propstat = 'de' AS DemolishedProperties),    
(select count(*) from pproperty where occcustno <> 0 AS OccupiedProperties);

First of all, these queries return syntax issues near 'AS' in lines 2+3 (subtotals of line 1) and 5+6+7 (subtotals of 4). I can't get past this as I have tried reformatting each time with or without brackets etc.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'AS'.

I need to produce a table with these headers and the totals/subtotals so don't know if this will work. I have another query that a former colleague created using NumberCheck to create a table, but it's a bit too complicated for me to replicate (and he is no longer here to ask for help).

Any help would be greatly appreciated. Thanks Liam

Answer 1

Try this:

create table ccustomer(floating int)
create table pproperty(occcustno int, propstat nvarchar(50) )
insert into ccustomer values (1),(0),(1),(0),(1),(0),(1),(0),(1),(1)
insert into pproperty values (0,'de'), (1,'de'), (0,'de'), (0,'us'), (1,'de'), (1,'us'), (1,'de')

select
(select count(*) from ccustomer) AS TotalCustomers,
(select count(*) from ccustomer where floating = 0) AS ActiveCustomers,    
(select count(*) from ccustomer where floating = 1) AS FloatingCustomers,    
(select count(*) from pproperty ) AS TotalProperties,    
(select count(*) from pproperty where occcustno = 0 and propstat <> 'de') AS VoidProperties,    
(select count(*) from pproperty where occcustno = 0 and propstat = 'de') AS DemolishedProperties,    
(select count(*) from pproperty where occcustno <> 0) AS OccupiedProperties;

In the script I created a sample input scenario:

Results: