[SOLVED] noob question: Is this possible?

[clearblue]

Hello!

 

This may be a real dumb question but here goes...

 

I have a table recording energy consumption that looks like this:

 

Site_id  From            To         kWh

1 2008-02-01 2008-02-29 278

1 2008-03-01 2008-03-31 298

1 2008-04-01 2008-04-30 284

2 2008-02-01 2008-02-29 24459

2 2008-03-01 2008-03-31 22550

2 2008-04-01 2008-04-30 21911

3 2008-02-01 2008-02-29 50571

3 2008-03-01 2008-03-31 53272

3 2008-04-01 2008-04-30 52271

 

I would very much like to know how to write a query that would display the information like this:

 

Site_id Feb-08 Mar-08 Apr-08

1          278      298      284

2        24459  22550  21911

3        50571  52710  51021

 

I am a real noob, so any help would be appreciated

 

 

[mezise]

Hi,

 

if there will be more consumption months in the table firstly you need to select grouped YY-MM of From column and then for each month select appropriate kWh values.

[clearblue]

Hi

 

Trying to follow your advice but getting nowhere. There will be more months and there are many more sites. I want to be able to specify sites and months.

 

This is what I thought you meant:

 

SELECT consumption.Site_id,

LEFT(consumption.`From`,7),

consumption.kWh

FROM

consumption

WHERE

consumption.Site_id IN  ('1', '2', '3') AND

consumption.`From` IN  ('2008-02-01', '2008-03-01', '2008-04-01')

GROUP BY

consumption.`From`

 

This is what is returned:

Site_id  LEFT(consumption.`From`,7)  kWh

1         2008-02                       278

1         2008-03                       298

1         2008-04                       284

 

 

 

 

 

[mezise]

What I meant was that you cannot do this in one query because there is no way (except creating MySQL procedure) to select dynamic number of columns based on number of YY-MM unique values in consumption table.

 

Query you have written is the first step, but it is too complicated, use just

SELECT
  LEFT(consumption.`From`,7) AS yymm
FROM
  consumption
GROUP BY
  yymm
ORDER BY
  yymm
;

Now you have number of month columns of your result table.

Using this data you may create (PHP?) select statement with appropriate number of columns.

 

[clearblue]

Beginning to get where your coming from,

SELECT
  LEFT(consumption.`From`,7) AS yymm
FROM
  consumption
WHERE
consumption.Site_id IN  ('1', '2', '3')
GROUP BY
  yymm
ORDER BY
  yymm

 

Returns

 

yymm

2008-02

2008-03

2008-04

 

But how do I use this to display

 

Site_id  2008-02  2008-03  2008-04

1            278      298        284

2          24459    22550    21911

3          50571    52710    51021

 

Thanks for your help btw, hope Im not bugging you. 

[mezise]

E.g. using PHP:

$selectCollumns = '`Site_id`';
foreach ($rows as $row) {
  $selectColumns .= '
  , (SELECT t2.`kWh` FROM consumption AS t2
     WHERE t2.`Site_id` = consumption.`Site_id`
       AND LEFT(`From`, 7) = "' . $row['yymm'] . '" LIMIT 1
    ) AS "' . $row['yymm'] . '"
  ';
}
$query = '
  SELECT
    ' . $selectCollumns . '
  FROM consumption
  WHERE
    LEFT(`From`, 7) IN ("2008-03", "2008-04")
  GROUP BY
    `Site_id`
  ORDER BY
    `Site_id`
  ;
';

 

Second query to execute should be:

 

SELECT
  `Site_id`
  , (SELECT t2.`kWh` FROM consumption AS t2
     WHERE t2.`Site_id` = consumption.`Site_id`
       AND LEFT(`From`, 7) = "2008-02" LIMIT 1
    ) AS "2008-02"
  , (SELECT t2.`kWh` FROM consumption AS t2
     WHERE t2.`Site_id` = consumption.`Site_id`
       AND LEFT(`From`, 7) = "2008-03" LIMIT 1
    ) AS "2008-03"
  , (SELECT t2.`kWh` FROM consumption AS t2
     WHERE t2.`Site_id` = consumption.`Site_id`
       AND LEFT(`From`, 7) = "2008-04" LIMIT 1
    ) AS "2008-04"
FROM consumption
WHERE
  consumption.`Site_id` IN  ('1', '2', '3')
GROUP BY
  `Site_id`
ORDER BY
  `Site_id`
;

[clearblue]

Thanks for the reply, I will have to do more reading as I know nothing about PHP. I have just started using mySQL (with Navicat) as an alternative to access and had hoped not to have to get into PHP just yet. I chose mySQL as multiple connections are part of my future plans but for now it would seem I will have to continue importing and exporting between excel and mySQL. I have saved the example you gave though and will attempt it once I know a bit more.

 

Once again thanks for your help.

[mezise]

If Site_id may be your input data consider using this ONE query:

SELECT
  LEFT(consumption.`From`, 7) AS 'Month'

  , (SELECT t2.`kWh` FROM consumption AS t2
     WHERE t2.`From` = consumption.`From`
           AND t2.`Site_id` = 1
     LIMIT 1) AS 'kWh of site 1'

  , (SELECT t2.`kWh` FROM consumption AS t2
     WHERE t2.`From` = consumption.`From`
           AND t2.`Site_id` = 2
     LIMIT 1) AS 'kWh of site 2'

  , (SELECT t2.`kWh` FROM consumption AS t2
     WHERE t2.`From` = consumption.`From`
           AND t2.`Site_id` = 3
     LIMIT 1) AS 'kWh of site 3'

FROM
  consumption
GROUP BY
  'Month'
ORDER BY
  'Month'
;

 

but the result will be as reversed table:

 

Month    kWh of site 1  kWh of site 2  kWh of site 3
-------  -------------  -------------  -------------
2008-02            278          24459          50571
2008-03            298          22550          53272
...

[PFMaBiSmAd]

You are making this harder than it needs to be and hard-coding queries with specific values. Also, for what you have shown, you don't want to use GROUP BY. That will condense identical rows into single rows.

 

While it is probably possible to create a complicated query to do this all in mysql, by just letting the database query for the wanted data and formatting the output (I'll assume you want a table since this tabular data) using PHP, the following simple query and code produces the results shown in the first post (tested) -

 

<?php
$table_name = "consumption";
$site_list = "1,2,3"; // a generic list of sites to query
$date_array = array(); // a generic list of dates to query
$date_array[] = '2008-02-01'; // fill in the dates
$date_array[] = '2008-03-01';
$date_array[] = '2008-04-01';
$date_list = "'". implode("','",$date_array) . "'"; // form the string necessary for the mysql IN() function
$query = "SELECT Site_id, DATE_FORMAT(`From`,'%b-%y') as monyy, kWh FROM $table_name WHERE Site_id IN($site_list) AND `From` IN($date_list) ORDER BY Site_id, `From`";

$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// form and output the results
$content = "<table border=\"1\">"; // start the table
$content .= "<tr><td>Site_id</td>"; // start the heading
// loop through the selected dates and make the date portion of the heading
foreach($date_array as $date)
{
$content .= "<td>" . date('M-y',strtotime($date)) . "</td>";
}
$content .= "</tr>"; // complete the heading
$last_site = ""; // variable to detect changes in the site number

// loop through all the data
while($row = mysql_fetch_assoc($result))
{
// detect when the site number changes
if($last_site != $row['Site_id'])
{
	// a new site number, start a new table row
	if($last_site != "")
	{
		// if there is already a last site, you need to finish that table row
		$content .= "</tr>";
	}
	$content .= "<tr><td>{$row['Site_id']}</td>";
	$last_site = $row['Site_id'];
}
$content .= "<td>{$row['kWh']}</td>";
}
$content .= "</tr></table>";
echo $content;
?>

[mezise]

You are making this harder than it needs to be...

While it is probably possible to create a complicated query...

 

PFMaBiSmAd please do not confuse complicated with comprehensive.

 

...to do this all in mysql

Thanks for the reply, I will have to do more reading as I know nothing about PHP. I have just started using mySQL (with Navicat) as an alternative to access and had hoped not to have to get into PHP just yet.

 

PFMaBiSmAd be constructive not provocative.

[clearblue]

Thanks to both mezise and PFMaBiSmAd. mezise is correct to say I would very much prefer resolve this through mySQL. However the tested PHP code provided by PFMaBiSmAd will be very usefull once I have learnt the basics of PHP and am able to get it to run. 

[PFMaBiSmAd]

Queries that contain specific blocks for and that reference each data value, that must be rewritten as the requested data changes, are not comprehensive. For a query for 100 or 1000 sites and dates for a whole year or for example the last 12 months or for the data from two years ago or the past three years, are you going to rewrite the query each time? That is not comprehensive.

[mezise]

For a query for 100 or 1000 sites...

Here I agree with your point. But it was the best I could figure out without using PROCEDURES.

Also I agree that using PHP you can just use simple selection of consumption data and then handle the data to appropriate display them.

 

... and dates for a whole year or for example the last 12 months or for the data from two years ago or the past three years, are you going to rewrite the query each time?

Here you are wrong. The last ONE query lets you analyze given sites for every month that exists in the consumption table. Additionally repeating site select part:

  , (SELECT t2.`kWh` FROM consumption AS t2
     WHERE t2.`From` = consumption.`From`
           AND t2.`Site_id` = 1
     LIMIT 1) AS 'kWh of site 1'

needs just replacing Site_id number.

 

That is not comprehensive.

For my current knowledge of MySQL it is. I do not want to be get overwise. If someone can provide more comprehensive SQL query on the subject I will be glad to learn something new and useful.

 

Greatings for all who like to play with SQL!

 

 

[fenway]

This is a standard cross-tab query... you can use PERIOD() to get the YYMM range (since they all seems to be full month), and then you can simply SUM() these (though there appears to be just a single entry).  No need for PHP at all.

[clearblue]

No need for PHP? Thats what I wanted to hear I will look up cross tab queries but any chance of a bit more detail? I made a £7000 cock up at work today and don't feel like I can get anything right...

[fenway]

Also known as a "pivot table" -- see this PDF.

[clearblue]

Hey thanks , had a quick look and yes that is exactly what I need. Will give it a proper read after work. Cheers!