June 6, 2012 at 5:10 am
Hey guys,
I have a table (MyTable) in the format;
Person (varchar)
Company (varchar)
Flag (bit)
(There are other fields but for the purposes of the query I'm trying to do, they're not required).
I need to get a list of each company, along with a total count of people who have each company name, and then a count of the number of people in each company where the bit is 1, and then 0.
I can query to get the individual results, but am struggling to get the results together in a single query;
i.e.
select company, COUNT(flag)
from MyTable
where Flag=1
GROUP BY COMPANY
I need to query so that column 1 has the list of companies, column 2 has the total record count for each company, column 3 has the total count for those in the company with the Flag (bit) = 1 and then finally column 4 to have the same but where the bit =0
I have tried nesting select statements, but get the error;
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
I'm assuming I'm doing something obviously wrong, but any help is much appreciated as always! 🙂
Thanks in advance for any responses.
June 6, 2012 at 5:21 am
June 6, 2012 at 5:30 am
No problem, there are multiple records using the same company, but a range of different companies
Example:
PERSON COMPANY BIT
Alice, Company1, 1
Bill, Company1, 1
Charlie, Company1, 0
David, Company2, 0
Eric, Company3, 1
Fred, Company1, 0
Gill, Company2, 1
Hannah, Company3, 0
Ian, Company4, 1
Jill, Company5, 1
Keith, Company2, 0
Lesley, Company1, 0
Manuel, Company6, 1
Nina, Company7, 0
Oyvind, Company7, 0
Peter, Company7, 1
Peter, Company1, 0
Peter, Company3, 1
etc
June 6, 2012 at 5:38 am
does the below fit your needs? added the 0/1 column on the end to see the if its bit = 0 or bit = 1
declare @tab table (PERSON nvarchar(10), COMPANY nvarchar(20), BIT BIT)
insert into @tab (PERSON, COMPANY, BIT) values
('Alice','Company1',1),
('Bill','Company1',1),
('Charlie','Company1',0),
('David','Company2',0),
('Eric','Company3',1),
('Fred','Company1',0),
('Gill','Company2',1),
('Hannah','Company3',0),
('Ian','Company4',1),
('Jill','Company5',1),
('Keith','Company2',0),
('Lesley','Company1',0),
('Manuel','Company6',1),
('Nina','Company7',0),
('Oyvind','Company7',0),
('Peter','Company7',1),
('Peter','Company1',0),
('Peter','Company3',1)
select
company, count(bit), 0
from
where bit = 0
group by company
union all
select
company, count(bit), 1
from
where bit = 1
group by company
order by 1
June 6, 2012 at 6:02 am
Many thanks Anthony, it's not quite in the format I was looking to use, but it does help a lot...
I was looking to try and get the format (for example);
COMPANY -- TOTAL COUNT -- BIT=1 COUNT -- BIT=0 COUNT
Company1 -- 151 -- 75 -- 76
Company2 -- 279 -- 221 -- 58
Company3 -- 1441 -- 945 -- 496
Company4 -- 12 -- 12 -- 0
I think I can probably use it to try and get what I was after though 🙂
I'll post the results if I'm able to get it working.
June 6, 2012 at 6:08 am
Using anthony.green's sample data table (note that posting sample data in an easily digestible format is always a big help for the people trying to help you):
; with cte as (
select Company
, sum(cast([BIT] as int)) as BIT_1
, sum(case when [BIT] = 0 then 1 else 0 end) as BIT_0
from @tab
group by Company
)
select Company,
BIT_1 + BIT_0 as TotalRecords
, BIT_1
, BIT_0
from cte
Edit:
In fact, you don't even need to pre-aggregate. You can just count up direct:
select Company
, count(*)
, sum(cast([BIT] as int)) as BIT_1
, sum(case when [BIT] = 0 then 1 else 0 end) as BIT_0
from @tab
group by Company
Should give you what you need.
Regards, Iain
June 6, 2012 at 6:16 am
Excellent, exactly what I was trying to get Iain! 🙂
Many thanks, much appreciated.
Apologies for the bad data sample, I'll be sure to add it in a more quickly usable format in any future posts.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply