Jump to content

Pivot Table MYSQL Query syntax...


Failing_Solutions

Recommended Posts

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 |

Link to comment
https://forums.phpfreaks.com/topic/273490-pivot-table-mysql-query-syntax/
Share on other sites

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.