Go Premium for a chance to win a PS4. Enter to Win

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

Can you do a count() in a table without grouping other columns

Basically what I am trying to do is get a table with all my cables, the number of conductors the cable is supposed to have, and the count of the cables in the table... To further illustrate here is the query I have

  SELECT CONDUCTOR.CABLE, TYPE_CODE.CONDUCTORS
    FROM CONDUCTOR, CABLE, TYPE_CODE
   WHERE CONDUCTOR.CABLE_ID = CABLE.CABLE_ID AND CABLE.ID = TYPE_CODE.ID
ORDER BY CABLE ASC;

The resulting table:
Cable      Conductors
1000A      3
1000A      3
1000B      3
1000B      3
1000B      3
1000C      3

what I want is this:
Cable      Conductors      Count(Cable)
1000A            3                     2
1000A            3                     2
1000B            3                     3
1000B            3                     3
1000B            3                     3
1000C            3                     1

Is this possible to do?
0
imonfireDAMMIT
Asked:
imonfireDAMMIT
1 Solution
 
slightwv (䄆 Netminder) Commented:
Try this (untested, just typed in):
 SELECT CONDUCTOR.CABLE, TYPE_CODE.CONDUCTORS,count(CONDUCTOR.CABLE) over(partition by CONDUCTOR.CABLE order by CONDUCTOR.CABLE) cable_count
    FROM CONDUCTOR, CABLE, TYPE_CODE
   WHERE CONDUCTOR.CABLE_ID = CABLE.CABLE_ID AND CABLE.ID = TYPE_CODE.ID
ORDER BY CABLE ASC;
0
 
imonfireDAMMITAuthor Commented:
Thanks. This worked perfectly.
0
 
sdstuberCommented:
the order by in the OVER() clause isn't necessary unless you are doing a rolling count
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now