Noob Oracle/SQL Question

Discussion in 'OT Technology' started by airbrat, Nov 21, 2003.

  1. airbrat

    airbrat Swollen Member

    Joined:
    May 6, 2001
    Messages:
    4,883
    Likes Received:
    0
    Location:
    Vegas
    how do I take the total count of one column and display the results in a new column?
     
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    in a result set?? I'm not sure what you mean really...

    you can always do a: SELECT COUNT([FieldName]) as [WhateverYouWantToNameTheColumn]
     
  3. airbrat

    airbrat Swollen Member

    Joined:
    May 6, 2001
    Messages:
    4,883
    Likes Received:
    0
    Location:
    Vegas
    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.
     
  4. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    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?
     
  5. airbrat

    airbrat Swollen Member

    Joined:
    May 6, 2001
    Messages:
    4,883
    Likes Received:
    0
    Location:
    Vegas
    yes, I just want to total the amount of records in that particular column and have the results shown on a new empty column.

    thanks
     
  6. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    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?
     
  7. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    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...
     
  8. airbrat

    airbrat Swollen Member

    Joined:
    May 6, 2001
    Messages:
    4,883
    Likes Received:
    0
    Location:
    Vegas
    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:

    Student____CRS1____CRS2___CRS1TTL____CRS2TTL
    Joe_______234555___234514_____4__________2____
    Jim_______435632___654674______________________
    Bill________65344_______________________________
    Jill________34522_______________________________

    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?
     
  9. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    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

    ie.

    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.
     
  10. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    Subselects. You'll need to do a subselect for each total you want. Don't have time to go into it though...
     

Share This Page