Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 group and sum problem

Author  Topic 

paleopoetry
Starting Member

6 Posts
Posted - 2010-07-28 : 01:19:06
Hello,

I have a problem with a school thing. Everything except for this detail is done so it has been very frustrating. I never got the hang of how to "think" SQL, I "think" C and then try to adapt to SQL, and that has been frustrating.

Anyway, I came up with this:

select supplier.number, supplier.name, quan*weight as totalweight
from supplier, supply, parts
where city in (select name from city
               where state = 'Mass')
and supplier.number = supply.supplier
and supply.part = parts.number
group by supplier.number, supplier.name, weight, quan;


NUMBER NAME TOTALWEIGHT
=========== ==================== =====================
89 Fisher-Price 450000
89 Fisher-Price 685000
475 DEC 10
475 DEC 640
475 DEC 450
475 DEC 1370

6 rows found

The last step is, to make two rows, with number, name, and (real) total weight. How is this done?

Thanks, EB

slimt_slimt
Aged Yak Warrior

746 Posts
Posted - 2010-07-28 : 01:23:30
i take your query and just add another query and you will get your desired results.

select
x.number
,x.name 
,sum(x.totalweight) as total_weight
from
(
select supplier.number, supplier.name, quan*weight as totalweight
from supplier, supply, parts
where city in (select name from city
               where state = 'Mass')
and supplier.number = supply.supplier
and supply.part = parts.number
group by supplier.number, supplier.name, weight, quan;
) as x
group by 
      x.name
     ,x.number
Go to Top of Page

paleopoetry
Starting Member

6 Posts
Posted - 2010-07-28 : 01:25:29
Ok, thanks man, I'll check that out in detail and hopefully learn something as well :)
Go to Top of Page

slimt_slimt
Aged Yak Warrior

746 Posts
Posted - 2010-07-28 : 01:31:56
If you can send your table schema, what is your SQL server version and some sample data, i'm sure we can do much better job :)

enjoy!
Go to Top of Page

paleopoetry
Starting Member

6 Posts
Posted - 2010-07-28 : 01:51:04
I use Mimer. It seems, this

select * from (select ...


is not possible in Mimer. But I see the logic in your code, so it should be possible to solve with - a view? I'll get back.
Go to Top of Page

slimt_slimt
Aged Yak Warrior

746 Posts
Posted - 2010-07-28 : 01:52:45
Post you table schema and some sample data so we will easier help you
Go to Top of Page

paleopoetry
Starting Member

6 Posts
Posted - 2010-07-28 : 02:16:56
Ok, I just wanted to try it out myself first.

I created a view of my first select, and then tried to access it. But it seems, this

select sum(totalweight) as total_weight, name, number


doesn't work as it mixes set functions (sum) with columns. (Strange - is this the case with views only or tables as well?) However, even if it did work, that would add *all* the weights in the table, I would only like the total weight of all the parts supplied by the companies from state "Mass", presented company by company. (In this case one row for Fisher-Price, and one row for DEC, *if* my first select was correct.)

Ok, you asked for it:

Schema:

-- The Jonson Brothers Ltd. company database
-- MIMER 8.2
-- Create the company database

-- Suppress error messages when dropping non-existing tables
-- WHENEVER ERROR CONTINUE;
-- SET OUTPUT OFF;
-- DROP TABLE supply CASCADE;
-- DROP TABLE debit CASCADE;
-- DROP TABLE sale CASCADE;
-- DROP TABLE parts CASCADE;
-- DROP TABLE item CASCADE;
-- DROP TABLE supplier CASCADE;
-- DROP TABLE dept CASCADE;
-- DROP TABLE store CASCADE;
-- DROP TABLE city CASCADE;
-- DROP TABLE employee CASCADE;
-- DROP TABLE months CASCADE;
-- DROP VIEW  sale_supply CASCADE;

-- Show error messages and exit
-- SET OUTPUT ON;
-- WHENEVER ERROR EXIT;

-- Schema definition starts here 

CREATE TABLE employee
	(number INTEGER CONSTRAINT pk_employee PRIMARY KEY,
	 name VARCHAR(20),
	 salary INTEGER,
	 manager INTEGER,
	 birthyear INTEGER,
	 startyear INTEGER);

CREATE TABLE dept
	(number INTEGER CONSTRAINT pk_dept PRIMARY KEY,
	 name VARCHAR(20),
	 store INTEGER NOT NULL,
	 floor INTEGER,
	 manager INTEGER);

CREATE TABLE item
        (number INTEGER CONSTRAINT pk_item PRIMARY KEY,
         name VARCHAR(20),
	 dept INTEGER NOT NULL,
         price INTEGER,
         qoh INTEGER CONSTRAINT ck_item_qoh CHECK (qoh >= 0),
         supplier INTEGER NOT NULL);

CREATE TABLE parts
	(number INTEGER CONSTRAINT pk_parts PRIMARY KEY,
	 name VARCHAR(20),
	 color VARCHAR(8),
	 weight INTEGER,
	 qoh INTEGER);

CREATE TABLE supply
	(supplier INTEGER NOT NULL,
	 part INTEGER NOT NULL,
	 shipdate DATE NOT NULL,
	 quan INTEGER,
	 CONSTRAINT pk_supply PRIMARY KEY (supplier, part, shipdate));

CREATE TABLE sale
	(debit INTEGER NOT NULL,
	 item INTEGER NOT NULL,
	 quantity INTEGER,
	 CONSTRAINT pk_sale PRIMARY KEY (debit, item));

CREATE TABLE debit
	(number INTEGER CONSTRAINT pk_debit PRIMARY KEY,
	 sdate DATE DEFAULT CURRENT_DATE NOT NULL,
	 employee INTEGER NOT NULL,
	 account INTEGER NOT NULL);

CREATE TABLE city
	(name VARCHAR(15) CONSTRAINT pk_city PRIMARY KEY,
	 state VARCHAR(6));

CREATE TABLE store
	(number INTEGER CONSTRAINT pk_store PRIMARY KEY,
	 city VARCHAR(15) NOT NULL);

CREATE TABLE supplier
	(number INTEGER CONSTRAINT pk_supplier PRIMARY KEY,
	 name VARCHAR(20),
	 city VARCHAR(15) NOT NULL);

-- Add foreign keys 

ALTER TABLE dept
	ADD CONSTRAINT fk_dept_store FOREIGN KEY (store) REFERENCES store 
(number);
ALTER TABLE dept
	ADD CONSTRAINT fk_dept_employee FOREIGN KEY (manager) REFERENCES employee 
(number)
        ON DELETE SET NULL;

ALTER TABLE item
	ADD CONSTRAINT fk_item_dept FOREIGN KEY (dept) REFERENCES dept (number);
ALTER TABLE item
	ADD CONSTRAINT fk_item_supplier FOREIGN KEY (supplier) REFERENCES supplier 
(number);

ALTER TABLE supply
	ADD CONSTRAINT fk_supply_supplier FOREIGN KEY (supplier) REFERENCES 
supplier (number);
ALTER TABLE supply
	ADD CONSTRAINT fk_supply_parts FOREIGN KEY (part) REFERENCES parts 
(number);

ALTER TABLE sale
	ADD CONSTRAINT fk_sale_item FOREIGN KEY (item) REFERENCES item (number);
ALTER TABLE sale
	ADD CONSTRAINT fk_sale_debit FOREIGN KEY (debit) REFERENCES debit(number);
-- implies that a debit/transaction must be created before a sale record.

ALTER TABLE debit
	ADD CONSTRAINT fk_debit_employee FOREIGN KEY (employee) REFERENCES 
employee (number);

ALTER TABLE store
	ADD CONSTRAINT fk_store_city FOREIGN KEY (city) REFERENCES city (name);

ALTER TABLE supplier
	ADD CONSTRAINT fk_supplier_city FOREIGN KEY (city) REFERENCES city (name);

-- Create the view that has to be modified in lab 2, question 17

CREATE VIEW sale_supply(supplier, item, quantity) as
	SELECT supplier.name, item.name, sale.quantity 
	FROM supplier, item, sale
	WHERE	supplier.number = item.supplier AND
		sale.item = item.number;



Data:

-- The Jonson Brothers Ltd. company database
-- MIMER 8.2
-- Populate the company database relations

-- The relation employee

INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(157, 'Jones, Tim', 12000, 199, 1940, 1960);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(1110, 'Smith, Paul', 6000, 33, 1952, 1973);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(35, 'Evans, Michael', 5000, 32, 1952, 1974);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(129, 'Thomas, Tom', 10000, 199, 1941, 1962);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(13, 'Edwards, Peter', 9000, 199, 1928, 1958);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(215, 'Collins, Joanne', 7000, 10, 1950, 1971);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(55, 'James, Mary', 12000, 199, 1920, 1969);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(26, 'Thompson, Bob', 13000, 199, 1930, 1970);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(98, 'Williams, Judy', 9000, 199, 1935, 1969);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(32, 'Smythe, Carol', 9050, 199, 1929, 1967);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(33, 'Hayes, Evelyn', 10100, 199, 1931, 1963);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(199, 'Bullock, J.D.', 27000, null, 1920, 1920);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(4901, 'Bailey, Chas M.', 8377, 32, 1956, 1975);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(843,  'Schmidt, Herman', 11204, 26, 1936, 1956);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(2398, 'Wallace, Maggie J.', 7880, 26, 1940, 1959);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(1639, 'Choy, Wanda', 11160, 55, 1947, 1970);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(5119, 'Bono, Sonny', 13621, 55, 1939, 1963);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(37, 'Raveen, Lemont', 11985, 26, 1950, 1974);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(5219, 'Schwarz, Jason B.', 13374, 33, 1944, 1959);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(1523, 'Zugnoni, Arthur A.', 19868, 129, 1928, 1949);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(430, 'Brunet, Paul C.', 17674, 129, 1938, 1959);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(994, 'Iwano, Masahiro', 15641, 129, 1944, 1970);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(1330, 'Onstad, Richard', 8779, 13, 1952, 1971);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(10, 'Ross, Stanley', 15908, 199, 1927, 1945);
INSERT INTO employee (number, name, salary, manager, birthyear, startyear)
	VALUES
	(11, 'Ross, Stuart', 12067, null, 1931, 1932);

-- internal error in MIMER
-- ALTER TABLE employee
--	ADD CONSTRAINT fk_emp_mgr FOREIGN KEY (manager) REFERENCES employee(number)
--	ON DELETE SET NULL;



-- The relation city

INSERT INTO city (name, state)
        VALUES
        ('Los Angeles', 'Calif');
INSERT INTO city (name, state)
        VALUES
        ('Oakland', 'Calif');
INSERT INTO city (name, state)
        VALUES
        ('El Cerrito', 'Calif');
INSERT INTO city (name, state)
        VALUES
        ('Atlanta', 'Ga');
INSERT INTO city (name, state)
        VALUES
        ('San Francisco', 'Calif');
INSERT INTO city (name, state)
        VALUES
        ('Boston', 'Mass');
INSERT INTO city (name, state)
        VALUES
        ('Dallas', 'Tex');
INSERT INTO city (name, state)
        VALUES
        ('Denver', 'Colo');
INSERT INTO city (name, state)
        VALUES
        ('White Plains', 'Neb');
INSERT INTO city (name, state)
        VALUES
        ('Amherst', 'Mass');
INSERT INTO city (name, state)
        VALUES
        ('Seattle', 'Wash');
INSERT INTO city (name, state)
        VALUES
        ('Paxton', 'Ill');
INSERT INTO city (name, state)
        VALUES
        ('New York', 'NY');
INSERT INTO city (name, state)
        VALUES
        ('San Diego', 'Calif');
INSERT INTO city (name, state)
        VALUES
        ('Hickville', 'Okla');
INSERT INTO city (name, state)
        VALUES
        ('Salt Lake City', 'Utah');
INSERT INTO city (name, state)
        VALUES
        ('Madison', 'Wisc');

-- COMMIT;


-- The relation store

INSERT INTO store (number, city)
	VALUES
	(5, 'San Francisco');
INSERT INTO store (number, city)
	VALUES
	(7, 'Oakland');
INSERT INTO store (number, city)
	VALUES
	(8, 'El Cerrito');


-- The relation dept


INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(35, 'Book', 5, 1, 55);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(10, 'Candy', 5, 1, 13);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(19, 'Furniture', 7, 4, 26);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(20, 'Major Appliances', 7, 4, 26);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(14, 'Jewelry', 8, 1, 33);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(43, 'Children''s', 8, 2, 32);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(65, 'Junior''s', 7, 3, 37);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(58, 'Men''s', 7, 2, 129);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(60, 'Sportswear', 5, 1, 10);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(99, 'Giftwrap', 5, 1, 98);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(1, 'Bargain', 5, 0, 37);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(26, 'Linens', 7, 3, 157);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(63, 'Women''s', 7, 3, 32);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(49, 'Toys', 8, 2, 35);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(70, 'Women''s', 5, 1, 10);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(73, 'Children''s', 5, 1, 10);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(34, 'Stationary', 5, 1, 33);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(47, 'Junior Miss', 7, 2, 129);
INSERT INTO dept (number, name, store, floor, manager)
	VALUES
	(28, 'Women''s', 8, 2, 32);
-- COMMIT;



--The relation supplier

INSERT INTO supplier (number, name, city)
	VALUES
	(199, 'Koret', 'Los Angeles');
INSERT INTO supplier (number, name, city)
	VALUES
	(213,'Cannon', 'Atlanta');
INSERT INTO supplier (number, name, city)
	VALUES
	(33, 'Levi-Strauss', 'San Francisco');
INSERT INTO supplier (number, name, city)
	VALUES
	(89, 'Fisher-Price', 'Boston');
INSERT INTO supplier (number, name, city)
	VALUES
	(125, 'Playskool', 'Dallas');
INSERT INTO supplier (number, name, city)
	VALUES
	(42, 'Whitman''s', 'Denver');
INSERT INTO supplier (number, name, city)
	VALUES
	(15, 'White Stag', 'White Plains');
INSERT INTO supplier (number, name, city)
	VALUES
	(475, 'DEC', 'Amherst');
INSERT INTO supplier (number, name, city)
	VALUES
	(122, 'White Paper', 'Seattle');
INSERT INTO supplier (number, name, city)
	VALUES
	(440, 'Spooley', 'Paxton');
INSERT INTO supplier (number, name, city)
	VALUES
	(241, 'IBM', 'New York');
INSERT INTO supplier (number, name, city)
	VALUES
	(62, 'Data General', 'Atlanta');
INSERT INTO supplier (number, name, city)
	VALUES
	(5, 'Amdahl', 'San Diego');
INSERT INTO supplier (number, name, city)
	VALUES
	(20, 'Wormley', 'Hickville');
INSERT INTO supplier (number, name, city)
	VALUES
	(67, 'Edger', 'Salt Lake City');
INSERT INTO supplier (number, name, city)
	VALUES
	(999, 'A E Neumann', 'Madison');
-- COMMIT;



-- The relation item

INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(26, 'Earrings', 14, 1000, 20, 199);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(118, 'Towels, Bath', 26, 250, 1000, 213);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(43, 'Maze', 49, 325, 200, 89);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(106, 'Clock Book', 49, 198, 150, 125);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(23, '1 lb Box', 10, 215, 100, 42);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(52, 'Jacket', 60, 3295, 300, 15);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(165, 'Jean', 65, 825, 500, 33);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(258, 'Shirt', 58, 650, 1200, 33);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(120, 'Twin Sheet',26, 800, 750, 213);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(301, 'Boy''s Jean Suit', 43, 1250, 500, 33);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(121, 'Queen Sheet', 26, 1375, 600, 213);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(101, 'Slacks', 63, 1600, 325, 15);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(115, 'Gold Ring', 14, 4995, 10, 199);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(25, '2 lb Box, Mix', 10, 450, 75, 42);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(119, 'Squeeze Ball', 49,  250, 400,  89);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(11, 'Wash Cloth', 1, 75, 575, 213);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(19, 'Bellbottoms', 43, 450, 600, 33);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	( 21, 'ABC Blocks', 1, 198, 405, 125);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(107, 'The ''Feel'' Book', 35, 225, 225, 89);
INSERT INTO item (number, name, dept, price, qoh, supplier)
	VALUES
	(127, 'Ski Jumpsuit', 65, 4350, 125, 15);
-- COMMIT;


-- The relation parts

INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(1, 'central processor', 'pink', 10, 1);
INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(2, 'memory', 'gray', 20, 32);
INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(3, 'disk drive', 'black', 685, 2);
INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(4, 'tape drive', 'black', 450, 4);
INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(5, 'tapes', 'gray', 1, 250);
INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(6, 'line printer', 'yellow', 578, 3);
INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(7, 'l-p paper', 'white', 15,  95);
INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(8, 'terminals', 'blue', 19, 15);
INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(13, 'paper tape reader', 'black', 107, 0);
INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(14, 'paper tape punch', 'black', 147, 0);
INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(9, 'terminal paper', 'white', 2, 350);
INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(10, 'byte-soap', 'clear', 0, 143);
INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(11, 'card reader', 'gray', 327, 0);
INSERT INTO parts (number, name, color, weight, qoh)
	VALUES
	(12, 'card punch', 'gray', 427, 0);
-- COMMIT;


-- The relation supply

create table months
	(name char(3) constraint pk_months primary key,
	 num char(2) unique);

insert into months(name, num) values ('JAN', '01');
insert into months(name, num) values ('FEB', '02');
insert into months(name, num) values ('MAR', '03');
insert into months(name, num) values ('APR', '04');
insert into months(name, num) values ('MAY', '05');
insert into months(name, num) values ('JUN', '06');
insert into months(name, num) values ('JUL', '07');
insert into months(name, num) values ('AUG', '08');
insert into months(name, num) values ('SEP', '09');
insert into months(name, num) values ('OCT', '10');
insert into months(name, num) values ('NOV', '11');
insert into months(name, num) values ('DEC', '12');

@
CREATE FUNCTION to_date(char_date CHAR(11))
RETURNS DATE
READS SQL DATA
BEGIN
  declare month_num char(2);
  declare month_name char(3);
  declare year_name char(4);
  declare day_name char(2);

  set year_name = substring(char_date from 8 for 4);  
  set month_name = substring(char_date from 4 for 3);
  select num into month_num from months where name = month_name;
  set day_name = substring(char_date from 1 for 2);  

  RETURN CAST(year_name || '-' || month_num || '-' || day_name AS DATE);
END
@

INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(475, 1, to_date('31-DEC-1993'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(475, 2, to_date('31-MAY-1994'),  32);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(475, 3, to_date('31-DEC-1993'), 2);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(475, 4, to_date('31-MAY-1994'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(122, 7, to_date('01-FEB-1995'), 144);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(122, 7, to_date('02-FEB-1995'), 48);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(122, 9, to_date('01-FEB-1995'), 144);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(440, 6, to_date('10-OCT-1994'), 2);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(241, 4, to_date('31-DEC-1993'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(62, 3, to_date('18-JUN-1994'), 3);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(475, 2, to_date('31-DEC-1993'), 32);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(475, 1, to_date('01-JUL-1994'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(5, 4, to_date('15-NOV-1994'), 3);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(5, 4, to_date('22-JAN-1995'), 6);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(20, 5, to_date('10-JAN-1995'), 20);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(20, 5, to_date('11-JAN-1995'), 75);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(241, 1, to_date('01-JUN-1995'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(241, 2, to_date('01-JUN-1995'), 32);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(241, 3, to_date('01-JUN-1995'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(67, 4, to_date('01-JUL-1995'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(999, 10, to_date('01-JAN-1996'), 144);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(241, 8, to_date('01-JUL-1995'), 1);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(241, 9, to_date('01-JUL-1995'), 144);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(89, 3, to_date('04-JUL-1995'), 1000);
INSERT INTO supply (supplier, part, shipdate, quan)
	VALUES
	(89, 4, to_date('04-JUL-1995'), 1000);
-- COMMIT;



-- The relation debit


INSERT INTO debit (number, sdate, employee, account)
	VALUES
	(100581, to_date('15-JAN-1995'), 157, 10000000);
INSERT INTO debit (number, sdate, employee, account)
	VALUES
	(100582, to_date('15-JAN-1995'), 1110, 14356540);
INSERT INTO debit (number, sdate, employee, account)
	VALUES
	(100586, to_date('16-JAN-1995'), 35, 14096831);
INSERT INTO debit (number, sdate, employee, account)
	VALUES
	(100592, to_date('17-JAN-1995'), 129, 10000000);
INSERT INTO debit (number, sdate, employee, account)
	VALUES
	(100593, to_date('18-JAN-1995'), 13, 11652133);
INSERT INTO debit (number, sdate, employee, account)
	VALUES
	(100594, to_date('18-JAN-1995'), 215, 12591815);
-- COMMIT;



-- The relation sale


INSERT INTO sale (debit, item, quantity)
	VALUES
	(100581, 118, 5);
INSERT INTO sale (debit, item, quantity)
	VALUES
	(100581, 120, 1);
INSERT INTO sale (debit, item, quantity)
	VALUES
	(100582, 26, 1);
INSERT INTO sale (debit, item, quantity)
	VALUES
	(100586, 127, 3);
INSERT INTO sale (debit, item, quantity)
	VALUES
	(100586, 106, 2);
INSERT INTO sale (debit, item, quantity)
	VALUES
	(100592, 258, 1);
INSERT INTO sale (debit, item, quantity)
	VALUES
	(100593, 23, 2);
INSERT INTO sale (debit, item, quantity)
	VALUES
	(100594, 52, 1);
-- COMMIT;

-- To see the contents of the database uncomment and execute the following lines

--SET OUTPUT ON;
--SET ECHO ON;
--SET PAGELENGTH 0;
--SET INPUT, OUTPUT LOG ON;
--LOG INPUT, OUTPUT ON 'company_contents.txt';

--SELECT * FROM employee;
--SELECT * FROM dept;
--SELECT * FROM item;
--SELECT * FROM parts;
--SELECT * FROM sale;
--SELECT * FROM debit;
--SELECT * FROM city;
--SELECT * FROM store;
--SELECT * FROM supply;
--SELECT * FROM supplier;
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-07-28 : 02:24:08
Just what should be the o/p of the following after you finish the last step?


NUMBER NAME TOTALWEIGHT
89 Fisher-Price 450000
89 Fisher-Price 685000
475 DEC 10
475 DEC 640
475 DEC 450
475 DEC 1370




Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH
Go to Top of Page

paleopoetry
Starting Member

6 Posts
Posted - 2010-07-28 : 02:28:07
Two rows, one for each company, and the weights added, like this (but calculated of course)

NUMBER NAME TOTALWEIGHT
89 Fisher-Price 450000+685000
475 DEC 10+640+450+1370
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-07-28 : 02:44:32
[code]

SELECT NUMBER,
NAME,
SUM(TOTALWEIGHT)AS TOTALWEIGHT FROM
(

PUT YOURORIGINAL QUERY HERE

)T

GROUP BY NUMBER,NAME

[/code]



Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH
Go to Top of Page

paleopoetry
Starting Member

6 Posts
Posted - 2010-07-28 : 02:53:07
Yes, this works! Thanks everyone!

But I don't understand the set function - where is it "limited", why doesn't it run the entire column?

whenever error continue;
set output off;
drop view test_view;
set output on;
whenever error exit;

create view test_view as
   select supplier.number, supplier.name, quan*weight as totalweight
   from supplier, supply, parts
   where city in (select name from city
                  where state = 'Mass')
   and supplier.number = supply.supplier
   and supply.part = parts.number
   group by supplier.number, supplier.name, weight, quan;

SELECT NUMBER, NAME, SUM(TOTALWEIGHT)AS TOTALWEIGHT FROM
test_view
GROUP BY NUMBER,NAME;
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site