Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Location
    new zealand
    Posts
    17

    Unanswered: can I add new sales and get a total ???????

    Hi there,

    I was wondering if this is possible:

    I need to update 'customers sales' in a database - can this be done so that when I enter the customers new sale to the database (via update page) it will add it to their current sales, giving them a new sales total.??



    Angie.
    Last edited by blondini3; 02-09-03 at 04:48.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: can I add new sales and get a total ???????

    Originally posted by blondini3
    Hi there,

    I was wondering if this is possible:

    I need to update 'customers sales' in a database - can this be done so that when I enter the customers new sale to the database (via update page) it will add it to their current sales, giving them a new sales total.??



    Angie.
    You could do that, using a database trigger. The syntax of a database trigger varies by DBMS.

    However, I wouldn't do that. Don't store the sales total at all. When you want to see a sales total, just SUM up the individual sales. You could have a view that does this for you, if you like:

    CREATE VIEW customer_total AS
    SELECT cust.cust_id, SUM(sales.value) tot_value
    FROM cust, sales
    WHERE cust.cust_id = sales.cust_id
    GROUP BY cust.cust_id;

    You are probably concerned that calculating the total sales "on demand" like this is bad for performance. But really, it is what databases are good at. Only very exceptionally is it necessary to store summed values - only in very big, high-access databases. And the downside of doing so is data integrity - i.e. you have to do a lot of work to ensure that the stored total is accurate (e.g. if a sale is deleted, updated).

  3. #3
    Join Date
    Feb 2003
    Location
    new zealand
    Posts
    17
    This works, thanks....BUT........when I go to addanother column from the Merlot table either before or after the SUM column, i get a ERROR MESSAGE saying 'column' not part of aggregate function !!!

    Can you only select two columns ??? I want it to display the CardRecordID, LastName, FirstName from Merlot Table, and total sales from Sales Table....but i can't get it to work...


    SELECT Merlot.CardRecordID, SUM(sales.sales_value)
    FROM Merlot, sales
    WHERE Merlot.CardRecordID = sales.CardRecordID
    GROUP BY Merlot.CardRecordID

    thanks....
    Last edited by blondini3; 02-10-03 at 22:47.

  4. #4
    Join Date
    Jan 2003
    Posts
    55
    It should be straight forward only..

    select Merlot.CardID, Merlot.firstname,Merlot.lastname, sum(sales.values)
    from Merlot,sales
    where......etc etc

    thats what i always do and works..mebbe its some syntax error like a comma or something..check it up..
    also sometiems the "group by" attribute causes problems so first jus see your output without grouping and then try again...

    And im a beginner at this too so dun count on my words..jus tryin to extend some help

    -s
    You try and try again..but then give up, there's no sense in being a complete fool about it!!!

  5. #5
    Join Date
    Feb 2003
    Location
    new zealand
    Posts
    17
    Thanks for your reply.....yeah, it should be straight forward allright, but it comes up STILL with an aggregate function error ?!!!!

    when i run this:

    SELECT Merlot.CardRecordID, Merlot.FirstName, Merlot.LastName, sum(Sales.Sales_Value)
    FROM Merlot,Sales
    WHERE Merlot.CardRecordID = Sales.CardRecordID

    I get an error message saying 'cardrecordID' not part of an aggregate function.

    BUT.......ALTHOUGH THIS ONE BELOW WORKS...it doesn't total up the sales for each recordID - it displays each cardID's sales individually....

    SELECT Merlot.CardRecordID, Merlot.FirstName, Merlot.LastName, Sales.Sales_Value
    FROM Merlot,Sales
    WHERE Merlot.CardRecordID = Sales.CardRecordID

    it won't let me add any more than two fields in the SELECT statment or it gives me the aggregate error.....because when I run this one below it totals the sales fine

    SELECT merlot.cardrecordid, sum(sales.sales_value)
    FROM merlot, sales
    WHERE merlot.cardrecordID = sales.cardrecordid

    how do I get it to total the sales for each cardID AND ALSO show more than two fields ??

    I'm using dreamweaver MX to create the queries.

    angie...
    Last edited by blondini3; 02-11-03 at 00:41.

  6. #6
    Join Date
    Feb 2003
    Location
    Chennai, India
    Posts
    2

    Try this

    SELECT Merlot.CardRecordID, Merlot.FirstName, Merlot.LastName, sum(Sales.Sales_Value)
    FROM Merlot,Sales
    WHERE Merlot.CardRecordID = Sales.CardRecordID
    group by Merlot.CardRecordID, Merlot.FirstName, Merlot.LastName

    Hope this will help u.

  7. #7
    Join Date
    Feb 2003
    Location
    new zealand
    Posts
    17
    THANK YOU THANK YOU THANK YOU KUMAR AP,,,,,,you are the best.............the best...................did i mention you're the greatest?????.................i will always, always, remember to put the other fields in the GROUP BY statement from now on....

    thankyou

    Angie.

  8. #8
    Join Date
    Feb 2003
    Location
    Chennai, India
    Posts
    2

    Cool Ur problem solved?.

    Hi,
    Whether your problem solved ?.. or else yet to be?.

  9. #9
    Join Date
    Feb 2003
    Location
    new zealand
    Posts
    17
    yep...it's all working now...thanks heaps.
    Angie.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •