The term supercomputer historically refers to a processing unit (which nowadays contains many processing cores) that is at the leading edge of computer processing technology and capability. However, the key criteria in addition to the cutting-edge technology that defines a "supercomputer" is that a large number of resources are connected together to assemble a processing unit with capabilities beyond the sum of the individual components, or what is ordinarily available to consumers. This means that supercomputers are expensive, and difficult to interconnect, configure, and operate. This makes them uncommon.
The recent release to the general public of multicore personal desktop and laptop computers has blurred the line between super and suped-up computers. SGI just announced the coming release of what they call personal supercomputers, 80-core machines priced at around $8000. Of course, the widespread availability and ease of use of such computers would beg the question of whether they remain cutting edge, and whether they still deserve the term "super".
Supercomputers are computers with very high computing capacity. A typical supercomputer has computing speed that is many thousand times faster than the typical personal computers used by individuals in their homes and offices. These super computers are very expensive, costing millions of dollars, because they need to be designed specially and manufactured individually. Personal Computers these days are quite economical because their high volume manufacture results in considerable economy of scale. These economy of scale are not available for manufacture of super computers.
Also super computers are very big in size requiring large facilities for their installation. They use lot of electricity and generate lot of heat. The Super computers installations need special arrangements to disperse this heat. Also super computers need specialized expertise for their operation and control.
All these features make use of super computers very expensive to use as compared to smaller computers, particularly the personal computers. Therefore, use of super computers is justified only for applications where the the combination of complexity of calculation, volume of computing to be done, and speed requirement cannot be handled by smaller computers. There are only limited such applications - for example, space programs, complex geological studies, weather studies and forecasting, and other complex scientific applications.
In summary we can say that super computers are uncommon because they are very expensive and difficult to operate. This extra cost and trouble of using them is justified for only limited applications requiring very high computing capacities and speed.