Discussion in 'OT Technology' started by airbrat, Nov 21, 2003.
how do I take the total count of one column and display the results in a new column?
in a result set?? I'm not sure what you mean really...
you can always do a: SELECT COUNT([FieldName]) as [WhateverYouWantToNameTheColumn]
sorry for the bad description. this is all brand new to me. Best way I can describe it is I have a table w/ all my records. I want to take one particular column, do a count of that particular column and report that total on the same table as a new result.
i'm assuming you're talking about a SUM() of the data, typically COUNT() is described as the number of rows returned...
now do you just want to run a select on the table and bring back the results to something?? or do you actually want to update the table with a NEW perminant column with the sum?
yes, I just want to total the amount of records in that particular column and have the results shown on a new empty column.
well, if you total'd the column, that would only give you 1 number, not 1 for each row since it's a table scan and complete SUM(). What is the application?? What are you really trying to do?
By default, just doing SUM([columntosum]) AS [newcolumnname] or COUNT([columntocount]) AS [newcolumnname] will create a new column for you. But also by default, the column is not saved to the table (the results are calculated on the fly).
In making SUM() and COUNT() work, you'll need to use the GROUP BY clause and include all the columns you wish to display. The trick here is GROUP BY is sensitive only to duplicates. So if you are selecting 15 columns and all the data for all 15 columns is unique, then GROUP BY won't have anything to group (since you will have told it to group all 15 columns). If you are only interested in the SUM() or COUNT() of one column, then you'll only be able to select that one column, do a group by on it, and then you'll have accurate results. Make sense?
Practical example: I want the total dollar amount of orders from my order table (more realistically, I'd want it within a time range for reporting, but lets keep it simple).
Supper simple table might be:
Column 1: OrderID (auto-increment for giggles)
Column 2: Product (text)
Column 3: Email address of purchaser (text)
Column 4: OrderAmount (int/double/float/money/whatever)
If you do:
SELECT OrderID, Product, Email, SUM(OrderAmount) FROM Orders GROUP BY OrderID, Product, Email, OrderAmount
You will not get the total amount of orders. What you will get is each record in table Orders. This doesn't work because OrderID is very unique and the other fields are pretty unique (you will have duplicates though).
So in this case, to get the total dollar amount from the table, you will need to simplify the query to something like this:
SELECT SUM(OrderAmount) AS Total FROM Orders GROUP BY OrderAmount
Here, we're going to group all the OrderAmount values together and SQL will add them up for you and create 1 row with 1 column named Total which will contain the total dollar amount of orders.
Now with some tweaking, you could do things like show the total dollar amount of orders per each individual customer (lets say by email address). So you might do:
SELECT Email, SUM(OrderAmount) AS Total FROM Orders GROUP BY Email, OrderAmount
Now all the duplicate email addresses (for multiple orders by the same person) grouped together and in doing so, each person's order will be summed up into one record. So you should expect the results to be all email unique addresses in the first column and the 2nd column containing the total dollar amount ordered with that email address.
Not sure if thats the direction you were trying to go in. If so, just play around with the GROUP BY and SELECT clauses. If you get stuck, you may need to consult some SQL manuals to look up the various tricks you can do...
This is a better example of what I'm trying to do.
I want to update an existing an table by taking the total count of one column and then showing those results as a new record. But the problem I 'm having is I want to show those new results on the first line of record. My table kind of looks like this:
This is a crude example but as you can see I have a total of 4 students. If you look at the 2nd column I have a total of 4 courses and if you look at the 3rd column I have a total of 2 courses.
What I want to do is take these course totals and show them in the next columns. As you can see in my example I have the new total shown as CRS1TTL and CRS2TTL. Btw, do you think I can be doing this a better way?
I would create another table.. Make another column in the first table with Student_id's and make that the Primary Key.. (I guess you can use student too).
Then have another column with the Foreign Key (student_id, or Student) then list the CRS1TTL and the third column being the total
Student_id Semester NumofClasses
11111111 CRS1TTL 4
22222222 CRS1TTL 3
11111111 CRS2TTL 2
Something like that would organize it a bit better.. I think some of the other guys can give you a better solution. But that is just my take.
Subselects. You'll need to do a subselect for each total you want. Don't have time to go into it though...