[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 168

# Count of occurences in table

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
vinux
• 3
• 2
• 2
• +3
2 Solutions

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

Commented:
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

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

cheers
0

Senior DBACommented:

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

Commented:
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

Commented:
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

Commented:
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

Senior DBACommented:
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

Senior DBACommented:
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

Commented:
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

Commented:
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

Commented:

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

## Featured Post

• 3
• 2
• 2
• +3
Tackle projects and never again get stuck behind a technical roadblock.