Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › MySQL Question -addition
New Posts  All Forums:Forum Nav:

MySQL Question -addition

post #1 of 5
4/16/12 at 7:21am
Thread Starter 
I'm stuck on a problem on my MySql homework. The question is:
Quote:
How many books has each customer ordered? List the customer number and the total number of books (a calculated field) ordered by the total number of books in descending order.


My thought is to update the quantity each time the database sees that (for example) customer number 1004 makes an order. BUT, how and where do I store this new variable without adding it to the database? I just want to display it.

The row names I'm dealing with are custid (customerID, an integer) and quant (quantity ordered, an integer)

Thanks
She Thinks My Hard Drives Sexy
(20 items)
  		  
CPUMotherboardGraphicsGraphics
Core i7 930 @ 4004Mhz 1.29 V Gigabyte GA-X58A-UD3R XFX Radeon 6970 nVidia 8800GTX 
RAMHard DriveOptical DriveOptical Drive
G. SKILL 6GB 1333Mhz @ 1455Mhz OCZ Vertex 3 120GB / Seagate 500GB Lite-On DVD-RW TSSTcorp DVD/RW 
CoolingOSMonitorMonitor
Prolimatech Megahalems Windows 7 x64 Professional Samsung S24A350H Dell 20" LCD 
MonitorMonitorKeyboardPower
MAG 17" LCD Samsung 50" Plasma  Focus FK-2001 Mechanical Corsair TX750 
CaseMouseMouse PadAudio
Cooler Master Storm Scout (Modded) Razer Abyssus Disney's Mickey Mouse with circuits JVC MX-GT700 
View all
  hide details  
Reply
She Thinks My Hard Drives Sexy
(20 items)
  		  
CPUMotherboardGraphicsGraphics
Core i7 930 @ 4004Mhz 1.29 V Gigabyte GA-X58A-UD3R XFX Radeon 6970 nVidia 8800GTX 
RAMHard DriveOptical DriveOptical Drive
G. SKILL 6GB 1333Mhz @ 1455Mhz OCZ Vertex 3 120GB / Seagate 500GB Lite-On DVD-RW TSSTcorp DVD/RW 
CoolingOSMonitorMonitor
Prolimatech Megahalems Windows 7 x64 Professional Samsung S24A350H Dell 20" LCD 
MonitorMonitorKeyboardPower
MAG 17" LCD Samsung 50" Plasma  Focus FK-2001 Mechanical Corsair TX750 
CaseMouseMouse PadAudio
Cooler Master Storm Scout (Modded) Razer Abyssus Disney's Mickey Mouse with circuits JVC MX-GT700 
View all
  hide details  
Reply
post #2 of 5
4/16/12 at 12:44pm
  • Joined: Feb 2010
  • Location: Stoney Creek
  • Posts: 4,406
  • Rep: 174 (Unique: 151)
  • Trader Rating: 21
  • Folding Team Rank: 375
  • Folding Team Name: The Misfits
  •  
    Name Team Rank Points / Jun Work Units / Jun
    onions 375 7,444,478 / 0 3,426 / 0
     
  • Select All Posts By This User
you have to store it in teh data base to display it
Reply
Reply
post #3 of 5
4/16/12 at 12:55pm
Look up the COUNT() and SUM() functions.

Also, it sounds like you're approaching this from a dynamic standpoint - keeping track of things as they are entered into the database - when a more typical task is to generate numbers based on data that has already been recorded.
Sanctuary 2
(15 items)
  		  
CPUMotherboardGraphicsRAM
Intel i5 3570K Asus Maximus V Gene eVGA GeForce 670 GTX 4x4GB Samsung LV DDR3 PC3 1600 
Hard DriveHard DriveOptical DriveCooling
2xSamsung 830 128GB SATA III SSD - RAID0 OCZ Agility 3 120GB SATA III SSD Asus 14x BD-R BDXL CoolerMaster Hyper 212 EVO 
OSOSMonitorKeyboard
Windows 7 Ultimate 64-bit Linux Fedora 17 64-bit BenQ XL2420T Cooler Master CM Storm Stealth 
PowerCaseMouse
Seasonic X-850W Fractal Design Arc Mini Logitech G700 
View all
  hide details  
Reply
Sanctuary 2
(15 items)
  		  
CPUMotherboardGraphicsRAM
Intel i5 3570K Asus Maximus V Gene eVGA GeForce 670 GTX 4x4GB Samsung LV DDR3 PC3 1600 
Hard DriveHard DriveOptical DriveCooling
2xSamsung 830 128GB SATA III SSD - RAID0 OCZ Agility 3 120GB SATA III SSD Asus 14x BD-R BDXL CoolerMaster Hyper 212 EVO 
OSOSMonitorKeyboard
Windows 7 Ultimate 64-bit Linux Fedora 17 64-bit BenQ XL2420T Cooler Master CM Storm Stealth 
PowerCaseMouse
Seasonic X-850W Fractal Design Arc Mini Logitech G700 
View all
  hide details  
Reply
post #4 of 5
4/16/12 at 12:57pm
  • Joined: Feb 2010
  • Location: Stoney Creek
  • Posts: 4,406
  • Rep: 174 (Unique: 151)
  • Trader Rating: 21
  • Folding Team Rank: 375
  • Folding Team Name: The Misfits
  •  
    Name Team Rank Points / Jun Work Units / Jun
    onions 375 7,444,478 / 0 3,426 / 0
     
  • Select All Posts By This User
Quote:
Originally Posted by Quasimojo View Post

Look up the COUNT() and SUM() functions.
Also, it sounds like you're approaching this from a dynamic standpoint - keeping track of things as they are entered into the database - when a more typical task is to generate numbers based on data that has already been recorded.

tahts what i was getting at
Reply
Reply
post #5 of 5
4/16/12 at 7:39pm
Having a column with the number of books ordered is completely unnecessary. Normally, this data would be generated dynamically. If you care to keep track of specific orders with a unique OrderID, you probably want to have four tables: Customers, Orders, Books, and BooksOrdered. If you don't care for keeping track of distinct orders, you can drop the Orders table and keep the other three.

To answer the question, you may either have a CustID in BooksOrdered, or you can get it by doing an INNER JOIN on Orders and BooksOrdered. You would then use COUNT() and GROUP BY. This will show the number of titles each customer ordered. Depending on whether or not you keep track of OrderID, it would look like one of these two:
Code:
SELECT CustID, COUNT(*) AS Total
FROM BooksOrdered
GROUP BY CustID
ORDER BY Total DESC

SELECT CustID, COUNT(*) AS Total
FROM Orders O INNER JOIN BooksOrdered B ON O.OrderID = B.OrderID
GROUP BY CustID
ORDER BY Total DESC

If you want to allow customers to order more than one of any given title in a single order, the BooksOrdered table would also have a field Quant. This is not the same as the Quant field you proposed, because it is not updated on an ongoing basis. This number represents the quantity ordered of a specific title in a given order. In this case you would use SUM() and the following queries would produce the desired result:
Code:
SELECT CustID, SUM(Quant) AS Total
FROM BooksOrdered
GROUP BY CustID
ORDER BY Total DESC

SELECT CustID, SUM(Quant) AS Total
FROM Orders O INNER JOIN BooksOrdered B ON O.OrderID = B.OrderID
GROUP BY CustID
ORDER BY Total DESC

Edited by ChaoticKinesis - 4/16/12 at 7:48pm
Main Rig
(20 items)
 
HTPC + Media Server + BOINC
(15 items)
 
Daughter's First PC
(13 items)
 
CPUMotherboardGraphicsRAM
Phenom II 1090T @3.8GHz, CPU-NB @3.0GHz Asus M5A99X EVO Sapphire HD 7950 Corsair Vengeance 16GB 
Hard DriveHard DriveHard DriveOptical Drive
Crucial M4 128GB 2 x Samsung F3 1TB (RAID 0) Seagate Barracuda 3TB Asus DVD-RW 
CoolingCoolingOSMonitor
CPU: Noctua NH-D14 GPU: Corsair H60 Windows 8 Pro Viewsonic VX2770SMH-LED 
MonitorKeyboardPowerCase
Samsung 943BWX Ducky YOTD (MX Brown) Lepa G700-MA Fractal Design Define R3 
MouseMouse PadAudioAudio
Roccat Savu Steelseries 9HD Beyerdynamic DT 880 Asus Xonar DG 
View all
CPUMotherboardGraphicsRAM
AMD A6-3500 Asrock A75M HD 6530D Samsung 4GB DDR3 1600  
Hard DriveHard DriveHard DriveOptical Drive
Intel 320 80GB Samsung Spinpoint F4 2TB WD Caviar Green 3TB Sony BD-Rom 
CoolingOSMonitorKeyboard
Noctua NH-C12P SE14 Win 7 Professional 64-bit Samsung LN46C600 Lenovo N5902 
PowerCaseOther
Corsair CX430 Antec Veris Fusion Hauppage WinTV DCR-2650 
View all
CPUMotherboardGraphicsRAM
AMD Phenom II X3 B55 Gigabyte GA-MA785GM-US2H MSI HD 5750 G.Skill 4GB DDR2 
Hard DriveOptical DriveCoolingMonitor
Samsung F3 1TB LG DVD-RW CM Hyper 212 Plus Dell U2312HM 
KeyboardPowerCaseMouse
Pink KBT Race (MX Blue) Corsair CX400 Corsair 500R Razer Abyssus 
Mouse Pad
Modware Radiance 
View all
  hide details  
Reply
Main Rig
(20 items)
 
HTPC + Media Server + BOINC
(15 items)
 
Daughter's First PC
(13 items)
 
CPUMotherboardGraphicsRAM
Phenom II 1090T @3.8GHz, CPU-NB @3.0GHz Asus M5A99X EVO Sapphire HD 7950 Corsair Vengeance 16GB 
Hard DriveHard DriveHard DriveOptical Drive
Crucial M4 128GB 2 x Samsung F3 1TB (RAID 0) Seagate Barracuda 3TB Asus DVD-RW 
CoolingCoolingOSMonitor
CPU: Noctua NH-D14 GPU: Corsair H60 Windows 8 Pro Viewsonic VX2770SMH-LED 
MonitorKeyboardPowerCase
Samsung 943BWX Ducky YOTD (MX Brown) Lepa G700-MA Fractal Design Define R3 
MouseMouse PadAudioAudio
Roccat Savu Steelseries 9HD Beyerdynamic DT 880 Asus Xonar DG 
View all
CPUMotherboardGraphicsRAM
AMD A6-3500 Asrock A75M HD 6530D Samsung 4GB DDR3 1600  
Hard DriveHard DriveHard DriveOptical Drive
Intel 320 80GB Samsung Spinpoint F4 2TB WD Caviar Green 3TB Sony BD-Rom 
CoolingOSMonitorKeyboard
Noctua NH-C12P SE14 Win 7 Professional 64-bit Samsung LN46C600 Lenovo N5902 
PowerCaseOther
Corsair CX430 Antec Veris Fusion Hauppage WinTV DCR-2650 
View all
CPUMotherboardGraphicsRAM
AMD Phenom II X3 B55 Gigabyte GA-MA785GM-US2H MSI HD 5750 G.Skill 4GB DDR2 
Hard DriveOptical DriveCoolingMonitor
Samsung F3 1TB LG DVD-RW CM Hyper 212 Plus Dell U2312HM 
KeyboardPowerCaseMouse
Pink KBT Race (MX Blue) Corsair CX400 Corsair 500R Razer Abyssus 
Mouse Pad
Modware Radiance 
View all
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › MySQL Question -addition