Solved

# Count of occurences in table

Posted on 2005-04-29
163 Views
Hi,
I have a table that looks like this:

Input       |  Output      |   Mask   |  Type    |Isactive
Input_1   |   Output _1 |  test       |  tag      |  1
Input_1   |   Output _2 |  test1       |  tag      |  1
Input_1   |   Output _3 |  test2       |  tag      |  1
Input_2   |   Output _1 |  test3      |  tag      |  1

I want of the all  rows with active =1  the following result:

Input     |   Output   |  Mask  | Type   |  (number of occurence of  that input in the resultset)

so for example:

Input_1   |   Output _1 |  test       |  tag        | 3
Input_1   |   Output _2 |  test1       |  tag      | 3
Input_1   |   Output _3 |  test2       |  tag      |  3
Input_2   |   Output _1 |  test3      |  tag      |  1

Could this be done with one query?
0
Question by:vinux

LVL 11

Expert Comment

select Input,  Output, Mask, Type   , count(Isactive ) as count_isactive
from mytable
group by Input,  Output, Mask, Type
0

LVL 34

Assisted Solution

SELECT A.Input, A.Output, A.Mask, A.Type, (SELECT Count(*) FROM myTable AS B WHERE B.Input = A.Input) AS ActiveCount
FROM myTable AS A
0

LVL 11

Expert Comment

didnt know your table name, but i guess you can change that yourself

cheers
0

LVL 68

Expert Comment

SELECT table1.Input, Output, Mask, Type, ActiveCount
FROM table1
INNER JOIN (
SELECT Input, COUNT(*) AS ActiveCount
FROM table1
GROUP BY Input
) AS t1Counts ON t1Counts.Input = table1.Input

0

LVL 28

Expert Comment

If I understand you correctly, here's how your query will look like:

SELECT Input, Output, Mask, Type, B.InputCount
FROM YourTable INNER JOIN (SELECT Input, COUNT(*) AS InputCount FROM YourTable
WHERE IsActive = 1
GROUP BY Input) B
ON A.Input = B.Input
0

LVL 34

Expert Comment

correction...

SELECT A.Input, A.Output, A.Mask, A.Type, (SELECT Count(*) FROM myTable AS B WHERE B.Input = A.Input) AS ActiveCount
FROM myTable AS A
WHERE A.IsActive = 1
0

LVL 28

Expert Comment

Correction also...

SELECT A.Input, Output, Mask, Type, B.InputCount
FROM YourTable INNER JOIN (SELECT Input, COUNT(*) AS InputCount FROM YourTable
WHERE IsActive = 1
GROUP BY Input) B
ON A.Input = B.Input
0

LVL 68

Accepted Solution

BriCrowe's method should also work.  Most often the JOIN method performs better, but it's not 100%, so you may want to check both and see which performs better in your particular situation.
0

LVL 68

Expert Comment

CORRECTION: (D'OH, left out the IsActive check in the subquery):

SELECT table1.Input, Output, Mask, Type, ActiveCount
FROM table1
INNER JOIN (
SELECT Input, COUNT(*) AS ActiveCount
FROM table1
WHERE IsActive = 1
GROUP BY Input
) AS t1Counts ON t1Counts.Input = table1.Input
0

LVL 5

Expert Comment

I'd go with BriCrowe, but add isactive in the subselect also....

SELECT A.Input, A.Output, A.Mask, A.Type, (SELECT Count(*) FROM myTable AS B WHERE B.Input = A.Input and isactive=1) AS ActiveCount
FROM myTable AS A
WHERE A.IsActive = 1
0

LVL 5

Expert Comment

Typo in last comment....

SELECT A.Input, A.Output, A.Mask, A.Type, (SELECT Count(*) FROM myTable AS B WHERE B.Input = A.Input and b.isactive=1) AS ActiveCount
FROM myTable AS A
WHERE A.IsActive = 1
0

LVL 3

Expert Comment

Try This :
select Input,Output, Mask, Type,Isactive , (Select count (input)from tablename group by input where isactive  =1 ) from tablename
0

## Featured Post

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.