Pivot Table MYSQL Query syntax...

[Failing_Solutions]

Hello gang,

 

I'm looking for help with my query

SELECT
IFNULL(worker_id,'Totals') AS Operator,	 -- outer query labels rollup row
sums.2012-11-26, sums.2012-11-27, sums.2012-11-28,	 -- and calculates horizontal sums
sums.2012-11-26 + sums.2012-11-27 + sums.2012-11-28 AS Sums
FROM (								 -- inner query groups by employee
SELECT								 -- with an expression for each column
worker_id,
SUM(IF(production_date=2012-11-26,production_net,0)) As '11-26-2012',
SUM(IF(production_date=2012-11-27,production_net,0)) As '11-27-2012',
SUM(IF(production_date=2012-11-28,production_net,0)) As '11-28-2012'
FROM production
GROUP BY worker_id WITH ROLLUP
) AS sums

 

 

This is my attempt to mimic the logic found here http://www.artfulsof...rytip.php?id=78

 

 

What I keep getting is errors like this

"Unknown column 'sums.2012-11-26' in 'field list'"

Which usually I assumed, meant I had not properly encapsulated the header names so I tried (sums.'2012-11-26' and 'sums.'2012-11-26' as well as variations with back ticks and no luck whatsoever.

 

 

Any help is very much appreciated..

 

 

This is how the data is stored in the database

id |production_date| production_net| worker_id
--------------------------------------------------
1 |2013-01-10	 | 390		 | MMahe
--------------------------------------------------
2 |2013-01-10	 | 400		 | RMaloney
--------------------------------------------------
3 |2013-01-11	 | 460		 | JBurris
--------------------------------------------------
4 |2013-01-11	 | 210		 | MMahe
--------------------------------------------------
5 |2013-01-14	 | 285		 | LTaylor
--------------------------------------------------

 

 

 

 

 

I'm wanting the results to look something like this..

operator| 2013-01-10| 2013-01-11| 2013-01-14| Sums |
----------------------------------------------------
MMahe | 390	 | 210	 |		 | 600 |
----------------------------------------------------
RMaloney| 400	 |		 |		 | 400 |
----------------------------------------------------
JBurris |		 | 460	 |		 | 460 |
----------------------------------------------------
LTaylor |		 |		 | 285	 | 285 |
----------------------------------------------------
Totals | 790	 | 670	 | 285	 |1745 |

[Barand]

backticks should do it

 

... As `11-26-2012`

 

and

 

sums.`11-26-2012`

[Failing_Solutions]

Hi Barand

 

Thanks for the response, I had thought I tried all varations of the backticks and apstrophes, but to be sure I ran them again with this query...

SELECT
IFNULL(worker_id,'Totals') AS Operator,
sums.`2012-11-26`, sums.`2012-11-27`, sums.`2012-11-28`,
sums.`2012-11-26` + sums.`2012-11-27` + sums.`2012-11-28` AS Sums
FROM (								
SELECT							
worker_id,
SUM(IF(production_date='2012-11-26',production_net,0)) As `11-26-2012`,
SUM(IF(production_date='2012-11-27',production_net,0)) As `11-27-2012`,
SUM(IF(production_date='2012-11-28',production_net,0)) As `11-28-2012`
FROM production
GROUP BY worker_id WITH ROLLUP
) AS sums

 

And still receive this error...#1054 - Unknown column 'sums.2012-11-26' in 'field list'

 

Whats more to further negate the issue with improperly encapsulated headers I decided to duplicate this table, and updated the date field to a varchar and remove the dashes so I end up with straight number values like this

id |production_date| production_net| worker_id

--------------------------------------------------

1 |20130110 | 390 | MMahe

--------------------------------------------------

2 |20130110 | 400 | RMaloney

--------------------------------------------------

3 |20130111 | 460 | JBurris

--------------------------------------------------

4 |20130111 | 210 | MMahe

--------------------------------------------------

5 |20130114 | 285 | LTaylor

-------------------------------------------------

 

Then ran this query

SELECT
IFNULL(worker_id,'Totals') AS Operator,
sums.20121126, sums.20121127, sums.20121128,
sums.20121126 + sums.20121127 + sums.20121128 AS Sums
FROM (								
SELECT							
worker_id,
SUM(IF(production_date=20121126,production_net,0)) As '11262012',
SUM(IF(production_date=20121127,production_net,0)) As '11272012',
SUM(IF(production_date=20121128,production_net,0)) As '11282012'
FROM production
GROUP BY worker_id WITH ROLLUP
) AS sums

 

And got the same error #1054 - Unknown column 'sums.20121126' in 'field list'

 

Any ideas ? attached is a the sql to create the table I am dealing with if that would help..

production.txt

Edited January 22, 2013 by Failing_Solutions

[Illusion]

You should use alias names as it is - in this case it doesn't magically know you are dealing with dates

11262012 is not same as 20121126

Edited January 22, 2013 by Illusion

[Barand]

Compare

 

sums.`2012-11-26`,

 

used in the SELECT clause with the alias you used in the sums subquery ie

 

... As `11-26-2012`,

[Failing_Solutions]

Thank you Illusion / Barand completly missed that.