Hi everybody,

I am new to database programming, started this week! I am using MySQL 5.0
with myphpadmin as the interface to it.

Basically I would like to know if there is some way to get a database to
perform automatic calculations, for instance, to make things simple, lets
say I have two tables - "Products" and "Sales".

In my Products table, I have a Primary Key for a field called "Catalogue
Number", this is unique to each product. All my product sales are listed in
the "Sales" table, where I also have the "Catalogue Number" field, as a
Foreign Key. I am using the "Catalogue Number" field to create a relation,
so that when I go back to my "Products" table I can get information from the
"Sales" table to furnish the field "Total Sold" - in this way I hope to
calculate how many of a particular "Catalogue Number" have sold, by
examining the relevant fields in the "Sales" table.

In spreadsheets I have used, and databases (Lotus Approach), I can use a SUM
IF function, to sum a particular column or row if it another specified
column or row contains a particular value. My syntax is probably way off,
but something allong the following lines is what I am refering to:

SUM IF A1-A100 = "RBX001", B1-B100

Cells A1-A100 contain the cell block (column) where the value "RBX001" is
searched for, and if there are occurences of that value at A32, A45, A73,
A92, & A97, the corresponding cells in cell block (column) B are summed.

So could I program a specific field in my databse with a function to do a
similar thing, so that I can examine the "Catalogue Number" field in my
"Sales" table for a particular value, and if the value occurs, sum (or
count) values in other correspnding fields.

Anyone know the MySQL function syntax for this if it is possible?

I am frantically reading, but so far a lot of it seems not relevant or way
over my head!

Regards,
CB.

Cerebral Believer wrote:
For example you can put an expression like this into a view:

(CASE WHEN col1 = 'RBX001'
THEN col2 + col3
ELSE 0 END)

http://dev.mysql.com/doc/refman/5.0/...functions.html

Try not to conceptualise in spreadsheet terms. A relational table is
nothing like a spreadsheet, except in the superficial sense that both
are often visualised as a grid.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Cerebral Believer wrote:
database engine. The user interface is the SQL language which
can be invoked directly from the command line or by embedding
SQL commands in programs written in a variety of languages.

What you want can be accomplished using SQL along the lines of:

select catalogue_number, count(*)
from sales
group by catalogue_number;

BTW, when talking about RDBMS we do not have fields and records.
We have columns and rows. I suspect this is because SQL
operations apply to complete sets of data rather than individual
data items. The sooner you start thinking about your tables as
collections or sets rather than individual records the sooner
you will become proficient using SQL.

HTH
Jerry

"David Portas" <REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org> wrote

He is using mysql not sqlserver and it is version 5.0

Jim Kennedy wrote:
What I posted was standard SQL92 and according to the MySQL docs CASE
is supported by version 5.0. I haven't tested it myself though.

--
David Portas

"David Portas" <REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org> wrote

David,

I couldn't get your code to work, modified to my needs, but after some trial
and error I found a statement which does the trick:

SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information` WHERE
`Catalogue Number` = "FBDCD001R";

Much simpler than I thought it would be. My problem now is that this works
only as a query, and I want to specify it as an automatic property (my
terminology) of a field/column. I am looking into creating functions and
proceedures to see if that will work.

Thanks for your earlier info BTW.

Regards,
CB.

"Jerry Gitomer" <jgitomer (AT) verizon (DOT) net> wrote

Thanks for your post Jerry,

I got through with:

SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information` WHERE
`Catalogue Number` = "FBDCD001R";

I want to see if I can specify that as a function or proceedure so that I
can select it from the function selector on the insert sub-tab in
myphpadmin. I hope this will enable the column to always hold the current
value for Quantity Sold (the sum total of "Quantity Ordered").

Regards,
C.B.

"Cerebral Believer" <nospamthanks (AT) hadenoughalready (DOT) com> wrote

OK,

I found the answer, again quite simple, but requiring back-tick quotes
(which I had not put round the procedure name), which is not specified on
the MySQL website, neither in the reference material or the examples. So
this works:

CREATE PROCEDURE `008_sales`()
SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information` WHERE
`Catalogue Number` = "FBDCD008";

Anyone know how I can get a column to automatically run that procedure? Is
that even possible?

Regards,
C.B.

Cerebral Believer wrote:
Another point of terminology, a TABLE is the implimentation of a
relation. When you combine data from two or more tables, it is a JOIN.

You really need to step away from your spreadsheet view and read about
the concepts of Relational Data Model. You might just start from the
beginning, read Codd's paper about this:
http://www.acm.org/classics/nov95/toc.html



If you get that procedure to run, where do you want the result to go?

Actually, unlike a spreadsheet, you do not want to run this
automatically. You probably want to get the SUM() for your report
screen. There is no reason to store this value since it can be
computed easily when needed, with the advantage that is is correct when
needed. So create a function to call or create a VIEW which your
report application can use.

HTH,
ed

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote

Thanks for that info, you are right I do visualise databases as being very
similar to databases. I don't see why a specific column/row entry can not
perform a function and return the value to a the same location. It seems
like protracted work to achieve the same things I could using a spreadsheet
but in database format - still I am determined to get there, and I will,
eventually!

To return the result to the same column/row, were it was called from. I
don't think this is possible though given what I have learned about
databases so far. Have you actually used myphpadmin? Anyway, I have been
experimenting with VIEW statements, and it seems like I am redesigning my
Totals table as a series of VIEW statements.

Yes, it is a different way of thinking, and perhaps more logical - that way
I can pull of a report of sales for a period, rather than just have an
ongoing running total.

Regards,
C.B.