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

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

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
Asked:
vinux
  • 3
  • 2
  • 2
  • +3
2 Solutions
 
lluthienCommented:
select Input,  Output, Mask, Type   , count(Isactive ) as count_isactive
from mytable
group by Input,  Output, Mask, Type  
0
 
Brian CroweCommented:
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
 
lluthienCommented:
didnt know your table name, but i guess you can change that yourself

cheers
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Scott PletcherSenior DBACommented:
Please try this:


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
 
rafranciscoCommented:
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
 
Brian CroweCommented:
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
 
rafranciscoCommented:
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
 
Scott PletcherSenior 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
 
Scott PletcherSenior 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
 
rmaranhaoCommented:
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
 
rmaranhaoCommented:
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
 
r_a_j_e_s_hCommented:

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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