SQL Query Help

[aeroswat]

Is there a way to create this query without having to create multiple queries?

 

I have a database of orders. Each order has a school system, value, and code attached to it. I need to create a report where each row will show the following:

• School System Name
  
• Total Amount of Orders for that System
  
• Total Value of the Orders for that System
  
• The Code that is being searched for
  

[Dennis1986]

You need to be more specific with what tables and fields you have and to speed things up, provide us with an example of result as well.

[aeroswat]

You need to be more specific with what tables and fields you have and to speed things up, provide us with an example of result as well.

 

I provided that? If there is some other way I can say it then please let me know...

 

Order Database

- School System (field)

- Value (field)

- Code (field)

 

I want it to pull up by each School System (I also have a database of all possible school systems)

Each line should have 1 school system for that school system it should have the total number of orders that school system has, the total value of all of those orders and the code for the orders (There is only one code so I should be able to pull up the last row's code to read this)

 

This is exactly what I said the first post ><

 

Example would be

 

Washington County            5 orders          $5,456.05        Code 4

Blackburne County              25 orders        $4,326.00        Code 4

[MatthewJ]

How about table names, field names, primary/foreign key fields... You know, the "details"

[inversesoft123]

post your code here. coz

 

1. we dont know how many tables you are using

2. and whats wrong if you use 2 queries.

[aeroswat]

post your code here. coz

 

1. we dont know how many tables you are using

2. and whats wrong if you use 2 queries.

 

I have no code. I need to know the query. Right now I have a very in-efficient work around that I wrote that cycles through every school system and makes a query for each school system. Here's the super specific details

 

tblOrders (holds order information)

tblSchools (holds school names)

 

tblOrders has the following fields

-SystemName (school system)

-Value (price of order)

-Code (Code that provides a description of the order)

 

tblSchools has the following fields

-systemName (school system)

[aeroswat]

If this will help my workaround looks like this

 

$sysresult = mysql_query("SELECT systemName FROM tblSchools");
$ordcount = 0;
$value = 0.00;

while($res=mysql_fetch_array($sysresult)) {
   $qry = substr($_SESSION['MultiOrderQry'],0,strrpos($_SESSION['MultiOrderQry'],"ORDER BY"));
   $qry .= "AND o.SchoolSystem='" . $res['systemName'] . "' ORDER BY SchoolSystem";
   $result = mysql_query($qry);

   while($ordres=mysql_fetch_array($result)) {
      $ordcount++;
      $value += $ordres['Cost'];
   }
   print systemname, ordcount, value (pseudo code)
   reset ordcount and value
}

 

[aeroswat]

Lol >< No one can answer my question now. Am I missing any more information?

[MatthewJ]

SELECT tblSchools.SystemName, count(tblOrders.SystemName) as c, sum(tblOrders.value) as v, tblOrders.code FROM tblSchools JOIN tblOrders ON tblSchools.SystemName = tblOrders.SystemName GROUP BY tblOrders.SystemName, tblOrders.code

 

That should be what you're after

[aeroswat]

SELECT tblSchools.SystemName, count(tblOrders.SystemName) as c, sum(tblOrders.value) as v, tblOrders.code FROM tblSchools JOIN tblOrders ON tblSchools.SystemName = tblOrders.SystemName GROUP BY tblOrders.SystemName, tblOrders.code

 

That should be what you're after

 

Thankyou very much sir! I will try this out soon as I can.