CODEWITHSUNDEEP
asp.netsqlsql-serverdatabase
Lars Holdgaard
Lars Holdgaard

Reputation: 9986

SQL Server - Group by, having and count in a mix

I have a database with a long list of records. Most of the columns have foreign keys to other tables.

Example:

ID SectorId BranchId
-- -------- --------
5  3        5

And then I will have a table with sectors, branches ect.

My issue:

I want to know how many records which has sector 1, 2, 3 ... n. So what I want is a group by Sector and then some count(*) which will tell me how many there is of each.

Expected output

So for instance, if I have 20 records the result might look like this:

SectorId Count
-------- -----
1        3
2        10
3        4
4        6

My attempts so far

I do not normally work a lot with databases and I have been trying to solve this for 1.5 hours. I have tried something like this:

SELECT COUNT(*)
FROM Records r
GROUP BY r.Sector
WHERE r.Date BETWEEN '2011-01-01' AND '2011-12-31'

But... errors and problems all over!

I would really appreciate some help. I do know this is probably very simple.

Thanks!

Upvotes: 1

Views: 2042

Answers (2)

Bhaskar saha
Bhaskar saha

Reputation: 11

Your query was partially right,But it needs some modification.

If I write this way:-

SELECT r.SectorID,COUNT(*) AS count
FROM Records r
WHERE r.Date BETWEEN '2011-01-01' AND '2011-12-31'
GROUP BY r.SectorID

Then output will be:-

SectorID  Count 
1           3
2          10
3           4
4           6

Upvotes: 1

Waqas Raja
Waqas Raja

Reputation: 10870

The sequence of your query is not correct; it should be like this: -

SELECT COUNT(*)
FROM Records r
WHERE r.Date BETWEEN '2011-01-01' AND '2011-12-31'
GROUP BY r.Sector

The output will be only counts i.e.

count
-----
3
10
4
6

If you want to fetch both sector and count then you need to modify the query a little

SELECT r.Sector, COUNT(*) as Count
FROM Records r
WHERE r.Date BETWEEN '2011-01-01' AND '2011-12-31'
GROUP BY r.Sector

The output will be like this: -

Sector Count
------ -----
1      3
2      10
3      4
3      6

Upvotes: 4

Related Questions