hosefo81

i\'m using mysql version 4.0.16 and i can\'t perform the rollup function. i would like to know what version is the function supported?

I want to find the customer with the highest sales for each category. Because i have to make seven queries to get the customer with the highest sales for each category by using the method below. Instead of making two or more queries by changing the where c.cat_id in each queries, can i get the result using only one queries? If yes, can someone please tell me how by correcting my code? select c.cat_id, s.cust_id, cu.cust_fname, cu.cust_lname, sum((sd.sales_price * sd.quantity)) as Total from product p, sales_detail sd, sales s, category c, brand b, customer cu where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.cat_id=b.brand_cat and p.prod_brand = b.brand_id and s.cust_id=cu.cust_id and year(s.sales_date) between \'1999\' and \'2003\' and c.cat_id=1 group by s.cust_id order by Total desc limit 1; select c.cat_id, s.cust_id, cu.cust_fname, cu.cust_lname, sum((sd.sales_price * sd.quantity)) as Total from product p, sales_detail sd, sales s, category c, brand b, customer cu where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.cat_id=b.brand_cat and p.prod_brand = b.brand_id and s.cust_id=cu.cust_id and year(s.sales_date) between \'1999\' and \'2003\' and c.cat_id=2 group by s.cust_id order by Total desc limit 1;

my tables are as below. CREATE TABLE PRODUCT ( PROD_ID VARCHAR(5) NOT NULL, PROD_NAME VARCHAR(20) NOT NULL, PROD_DESC VARCHAR(20) NOT NULL, PROD_PRICE DECIMAL NOT NULL, PROD_CAT INT NOT NULL, PRIMARY KEY(PROD_ID), FOREIGN KEY(PROD_CAT) REFERENCES CATEGORY, ); CREATE TABLE SALES ( SALES_ID INT NOT NULL, SALES_DATE DATE NOT NULL, CUST_ID VARCHAR(5) NOT NULL, EMP_ID VARCHAR(6) NOT NULL, PRIMARY KEY(SALES_ID), FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER, FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE, ); CREATE TABLE SALES_DETAIL ( SALES_ID INT NOT NULL, PROD_ID VARCHAR(5) NOT NULL, QUANTITY INT NOT NULL, PRIMARY KEY (SALES_ID, PROD_ID), ); if some of the product have discount for a duration of some time, should i create a product table? CREATE TABLE DISCOUNT ( DISCOUNT_ID INT NOT NULL, PROD_ID VARCHAR(5) NOT NULL, START_DATE DATE NOT NULL, END_DATE DATE NOT NULL, ); This is my sql queries to count total sales without the discount table. if the discount table is created, how am i going to make changes to the query to count the total after deducted the discount? select month(s.sales_date), s.cust_id, sum((p.prod_price * sd.quantity)) as Total from product p, sales_detail sd, sales s, category c where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.category_id=p.prod_cat and year(s.sales_date) = \'1999\' group by month(s.sales_date), s.cust_id;

i\'m building a Sales and Marketing Executive Information System. So i need to do forecasting but i don\'t have any idea how to do the forecasting part.

Below are a few of my mysql tables. I have problem with the sales_detail table. How can i make the prod_id in the sales_detail table reference the prod_id in the product table? i\'m confused as the sales_id and prod_id in sales details are primary key. Can primary key be reference? I have this problem because i once accidentally enter a prod_id in sales_details that does not exist in the product table and the database accepts it without error. How can i correct this situation? If yes, how can i alter the sales_details table? CREATE TABLE PRODUCT ( PROD_ID VARCHAR(5) NOT NULL, PROD_NAME VARCHAR(20) NOT NULL, PROD_DESC VARCHAR(20) NOT NULL, PROD_PRICE DECIMAL NOT NULL, PROD_CAT INT NOT NULL, PRIMARY KEY(PROD_ID), FOREIGN KEY(PROD_CAT) REFERENCES CATEGORY, ); CREATE TABLE SALES ( SALES_ID INT NOT NULL, SALES_DATE DATE NOT NULL, CUST_ID VARCHAR(5) NOT NULL, EMP_ID VARCHAR(6) NOT NULL, PRIMARY KEY(SALES_ID), FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER, FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE, ); CREATE TABLE SALES_DETAIL ( SALES_ID INT NOT NULL, PROD_ID VARCHAR(5) NOT NULL, QUANTITY INT NOT NULL, PRIMARY KEY (SALES_ID, PROD_ID), );

Do i need to build a table for sales forecast? if yes, what should i include in it? i already have table product, sales, sales details.

i am building a system using php, mysql, apache, jpgraph on Windows xp using notepad. my system are connected to the database, can generate graph and function properly. But i want to use Dreamweaver Mx instead of coding in the notepad. So after installing the dreamweaver Mx, What do i need to do in order to make my system run?is there any configuration that i need to make to the php.ini, jpgraph.php, mysql or in the dreamweaver to make it run? Please list out the steps or recommend a site for me to install and configure it?

i successfully list out the customer that buy my product every month using mysql. But i can\'t list out the customer that have the highest sales for every month. please help. Below are my sql command. select month(s.sales_date), s.cust_id, sum((p.prod_price * sd.quantity)) as Total from product p, sales_detail sd, sales s, category c where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.category_id=p.prod_cat and year(s.sales_date) = \'1999\' group by month(s.sales_date), s.cust_id; +---------------------+---------+-------+ | month(s.sales_date) | cust_id | Total | +---------------------+---------+-------+ | 1 | C001 | 134 | | 1 | C004 | 162 | | 2 | C001 | 390 | | 2 | C020 | 354 | | 3 | C001 | 522 | | 3 | C002 | 153 | | 3 | C013 | 93 | | 3 | C014 | 50 | | 3 | C020 | 321 | | 4 | C001 | 493 | | 4 | C012 | 33 | | 4 | C013 | 36 | | 4 | C014 | 197 | | 5 | C010 | 487 | | 5 | C011 | 461 | | 5 | C020 | 430 | | 6 | C001 | 667 | | 6 | C020 | 289 | | 7 | C001 | 44 | | 7 | C019 | 178 | | 7 | C020 | 78 | | 8 | C001 | 125 | | 9 | C001 | 144 | | 9 | C002 | 78 | | 9 | C003 | 36 | | 10 | C001 | 250 | | 10 | C018 | 304 | | 10 | C019 | 210 | | 10 | C020 | 308 | | 11 | C001 | 346 | | 11 | C020 | 68 | | 12 | C001 | 203 | | 12 | C002 | 220 | |+---------------------+---------+---- i try to code it like this but to no avail. please help select month(s.sales_date), s.cust_id, Max(sum((p.prod_price * sd.quantity)) )as Total from product p, sales_detail sd, sales s, category c where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.category_id=p.prod_cat and year(s.sales_date) = \'1999\' group by month(s.sales_date), s.cust_id;