Count function

SQL Server Administration
1

Hello,
I'm new using SQL server management studio and I have a (probably very) simple question.

I've got a database with some columns amongst other, a simplified example:
LocationId / Amount:
728 / 5
728 / 4
730 / 0
730 / 43
735 / 0

What I want, is a COUNT function that counts if the Amount > 0 per location. So the output should be:
728 - 2
730 - 1

Of course, the real problem is more complex with more locations and rows, but this is the essence. I know how to count for one location, however to create a query with all locations I have some trouble.

Can anybody help me, is this possible in SQL or should I use a different program?

Best regards,
Robbert

2

Create table #t (LocationId int,
Amount int)
insert into #t (LocationId, Amount) values
(728,5),
(728,4),
(730,0),
(730,43),
(735,0)

select LocationID, count(1) as N
from #t
where Amount > 0
group by LocationID

1 Like
3

Would this work?

declare @mushroom table(LocationId int, Amount int)
insert into @mushroom
select 728 , 5		 union
select 728 , 4		 union
select 730 , 0		 union
select 730 , 43		 union
select 735 , 0		 


select count(1), LocationId 
from @mushroom 
where Amount > 0 group by LocationId