My query returns a table of results, I would like to add a count column
that contains the number of each result type returned.
i.e.
Type Count
1 3
1 3
1 3
2 2
2 2
3 4
3 4
3 4
3 4
4 2
4 2
Because there are 3 of type 1, 2 of type 2, 4 of type 3 etc...
Is there straightforward way of doing this in SQL?
Thanks
1  1286 
In SQL Server 2005:
select
[Type],
count() over (partition by [Type]) as [Count]
from T
In SQL Server 2000:
select
[Type],
(select count(*)
from T as Tcopy
where Tcopy.[Type] = T.[Type]
) as [Count]
from T
(both solutions untested - for a better chance at tested
solutions, include create table and insert statements that
can be cut and pasted into a query editor.)
Steve Kass
Drew University ka*********@yah oo.com wrote:
My query returns a table of results, I would like to add a count column
that contains the number of each result type returned.
i.e.
Type Count
1 3
1 3
1 3
2 2
2 2
3 4
3 4
3 4
3 4
4 2
4 2
Because there are 3 of type 1, 2 of type 2, 4 of type 3 etc...
Is there straightforward way of doing this in SQL?
Thanks
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Bryan |
last post by:
I've been trying to work out a problem but I'm having no luck. I have
field in my table for area and I need to count the number of matches
and assign a variable for each one.. this is what I have but it doesn't
work.. any ideas?
$testcount = mysql_query("select area, count(area) as newcount from rea
group by area order by area");
while ($returncount = mysql_fetch_array($testcount)) {
if ($returncount == 'RCC') {
$rccn = $returncount;
|
by: Michael Krzepkowski |
last post by:
All,
I have a view that returns the following values:
Item Vendor
70807 1234
70807 5678
If I am looking for items that have more than one vendor:
select item_num,count(*)
|
by: Good Man |
last post by:
Hi all
Well, I didn't want to have to bring this question to someone else to
answer, but I am truly flummoxed and could use some help. It all boils
down to trying to find the number of jobs available in a particular
state. I have two tables, one called STATES and one called WORK. Here
are excerpts from both:
STATES table:
StateID | StateName | StateAbbr
|
by: vacataire testeur de site web |
last post by:
Bonjour je cherche à afficher de count de tables identiques dans la forme
exemple
num_cli | nombre de poste | nombre de voiture
1 1 3
2 2 5
3 1 0
45 4 6
|
by: Matthew Groch |
last post by:
Hi all,
I've got a server that handles a relatively high number of concurrent
transactions (on the magnitude of 1000's per second). Client
applications establish socket connections with the server. Data is
sent and received over these connections using the asynchronous model.
The server is currently in beta testing. Sporadically over the course
of the day, I'll observe the thread count on the process (via perfmon)
start climbing....
| | |
by: Auddog |
last post by:
I have the following query that works in mysql:
select id, order_no, price, count(item_no), sum(price) from production
WHERE item_no = '27714'
group by item_no;
When I setup my query in php, I use:
$query2 = "SELECT id, order_no, price, count(item_no) as count from
production where item_no = '27714";
|
by: ret4rt |
last post by:
Hello.
I have a database with movies similar with imdb and i want to find out which directors have directed both thriller and drama movies.
The output i want is like this "DIR_NAME,amount of thriller movies,amout of drama movies"
First i did a check to see the expected number.Checkin only directors that have made thriller movies with
select DIR_NAME, count(GENRE)
from MGENRES, DIRECTORS, DIRECTED_BY
where GENRE = 'Thriller' and...
|
by: muld |
last post by:
I've inherited a database system designed to calculate sickness absence statistics. There is a main table with an employee ID which also holds the number of days worked by that person in a year . There is then a table which records absences for each employee, it records the date of the absence etc and also holds the number of days absent . Each time the employee is absent they will have an entry in this table linked by . I have created a query...
|
by: jaxkookie |
last post by:
I have been trying to count each answer for each question and roll this up to one line item per question with totals for each answer. what I am getting is staggered answers. example, question 1 has 9 possible answers. I get the count for each, but question 1 is repeated 9 times. here is the part of the code I get the stagger results with:
CASE WHEN ANSWER_BANK.ANSWER_SEQUENCE < 1 THEN COUNT(ANSWER_BANK.ANSWER_SEQUENCE) END AS NO_ANS,
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
