Search Marketing

   
   

How To Use Nested Select Query

Wed Apr 27, 2011 11:04 pm
<<     >>
Comments: 2 Views: 15687

Nested Select Statements in MySQL allow you to multiply the ways you can search tables.

A simple nested select statement might look complicated but really you are simply using one table in a database to count against another. Many people who are used to PHP type scripting languages think about data gathering in a linear way. Using MySQL in command line you can't do loops, so the statements used are generally simple. But using nested select statements allows you to avoid programming loops and the commands tend to be cleaner.

If you have the following database table:

customer_history

  • ID
  • Customer_Name
  • Date
  • Payment_Amount

Query: Find, by day the total revenue - for all dates that exist.

Line 1: select
Line 2: Distinct left(ch.Date,10) as OpenDate,
Line 3: ( select sum(ch.Payment_Amount) from customer_history ch where left(ch.Date,10)=OpenDate ) as "Revenue"
Line 4: from customer_history ch
Line 5: where ch.Date like '%SomeDate%'
Line 6: order by OpenDate;

The second line (Distinct left(ch.Date,10) as OpenDate) determines what distinct dates exist in the data. To differentiate the Dates it is aliased as OpenDate.

The third line (( select sum(ch.Payment_Amount) from customer_history ch where left(ch.Date,10)=OpenDate ) as "Revenue" ) uses that distinct date to add up all the Payment_Amounts, and is now called Revenue.

The fifth line is like a date range, between two different dates could also be used.



Comments
Sat Jan 01, 2011 6:13 am
Name: paul | Comment: line 3 needs another right parenthesis

Wed Apr 27, 2011 11:04 pm
Name: Nik | Comment: select Date, sum(Payment_Amount) from customer_history GROUP BY Date order by Date;

Name:
*
URL: http: (ex. cnn.com)
Comment:
*
Number:
Math (10 + 5)
* required
SubCategories
Books

© 2017 Christonium LLC

Christonium.com
|
Terms of Use
|
Privacy
ccc