Need help with a count.
-
Hi, I've been struggling with a pretty simple count...
Ive got a table: companies -> |id|branch|groupid|
I want to count id:s of all companies, group them by branch (which is quite simple).
The problem is i want to count companies with same groupid as single "company".
Also companies without a group have default groupid value 0.
Any thoughts?
-
Thanks to Lowell for the image
-
have a look at "grouping sets" in books online
or at
Technet and Technet too
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
CREATE TABLE [companies](
[id] [int] IDENTITY(1,1) NOT NULL,
[branch] [nvarchar](50) NULL,
[groupid] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [companies] ADD CONSTRAINT [DF_companies_groupid] DEFAULT ((0)) FOR [groupid]
GO
INSERT INTO companies (branch, groupid) values('branch1', 0)
INSERT INTO companies (branch, groupid) values('branch1', 0)
INSERT INTO companies (branch, groupid) values('branch2', 1)
INSERT INTO companies (branch, groupid) values('branch2', 1)
INSERT INTO companies (branch, groupid) values('branch3', 1)
INSERT INTO companies (branch, groupid) values('branch4', 2)
INSERT INTO companies (branch, groupid) values('branch4', 0)
desired result would be:
branch1 | 2
branch2 | 1
branch3 | 1
branch4 | 2
-
SELECT branch, count(groupid) as num from companies group by branch
-
SELECT branch, count(groupid) as num from companies group by branch
gives this set:
branch11
branch22
branch31
branch42
btw, added another tuple to make it more clear.
-
Thats right, you have two records for branch 2 in the sample data
But checking your expected results you only want 1, what is the logic to only get 1 and not 2
-
Two companies have the same groupid, (they are a part of a bigger company), so i want to count them as one company.
groupid=0 represents "no group". So two companies with groupid=0 should be counted as two.
-
i have a question why branch1 has a groupid of 0 both times and branch4 has a group id of 2 and 0. i think a data set a little more representitive of the problem will be very helpful here.
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
;WITH CTE AS
(
SELECT DISTINCT branch, groupid FROM companies where groupid <> 0
)
SELECT
branch,
sum(num)
FROM
(
SELECT branch, count(groupid) as num FROM companies where groupid = 0 group by branch
union all
SELECT branch, count(groupid) as num FROM cte group by branch
) as dev1
group by branch
-
Nice! This did the trick.
Oh, thought it looked easy but, your query had pretty complex sql structure (atleast for my current level).
I just have to learn CTE...*sigh*
-
You can avoid the CTE and use a Derived table as well as follows:
Select branch, Sum(Case When rn <= 1 Then 1 Else 0 End) As Count From
(Select *, (Case When groupid = 0 Then 0 Else ROW_NUMBER() Over (Partition By Branch Order By
Id) End) As rn
From Companies) As a
Group By branch
But, the logic is the same as using a CTE.
With all due respect Mr. Anthony Green, I'm not taking away anything from you. Just showing the OP how to do it without a CTE. 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply