Generalising a date-related query

Thanks Rudy:

what software will you be using to produce the chart? That’s done in PHP, pulling data directly from the mySQL db.

also, how many currencies do you have?

I don’t have any, but hopefully, my client will. Probably good enough to use $US, €Euro, UK Pounds - possibly I should throw in Aus$ and Can$ for good luck.

David

Sorry about the cross posts. Hopefully, that’s fixed. (Thanks Ralph).

But Rudy, given all the confusion I managed to create, could you restate once more a basic solution?

Again, I have a field print.sales. Another field defines currency.

I want to simply create total sales by year. I can do this now by year and currency, but cannot figure out how to combine these totals properly…

Many thanks…

David

I am posting the entire mySQL call so you can see what I’m trying to do. There are three things wrong:

  1. It’s ugly,
  2. It’s really long, and
  3. it doesn’t work…

Other than that, it’s ok…

$sql = "SELECT
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 10) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard1 ,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 9) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as year2,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 8) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard3,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 7) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard4,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 6) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard5,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 5) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard6,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 4) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard7,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 3) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard8,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 2) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard9,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 1) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard10,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = $year AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard11,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year + 1) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard12,

(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 10) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare1 ,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 9) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare2,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 8) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare3,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 7) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare4,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 6) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare5,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 5) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare6,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 4) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare7,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 3) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare8,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 2) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare9,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 1) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare10,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = $year AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare11,
(SELECT SUM(prints.print_key) FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year + 1) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare12,


(SELECT yeard1 + (yeare1 * $euro) from prints as year1),
(SELECT yeard2 + (yeare2 * $euro) from prints as year2),
(SELECT yeard3 + (yeare3* $euro) from prints as year3),
(SELECT yeard4 + (yeare4 * $euro) from prints as year4),
(SELECT yeard5 + (yeare5 * $euro) from prints as year5),
(SELECT yeard6 + (yeare6 * $euro) from prints as year6),
(SELECT yeard7 + (yeare7 * $euro) from prints as year7),
(SELECT yeard8 + (yeare8 * $euro) from prints as year8),
(SELECT yeard9 + (yeare9 * $euro) from prints as year9),
(SELECT yeard10 + (yeare10 * $euro) from prints as year10),
(SELECT yeard11 + (yeare11 * $euro) from prints as year12),
(SELECT yeard12 + (yeare12 * $euro) from prints as year12)
FROM prints
GROUP BY year1, year2, year3, year4 ";

This is the code output:

Errors :

1) SELECT (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 10) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard1 , (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 9) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as year2, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 8) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard3, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 7) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard4, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 6) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard5, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 5) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard6, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 4) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard7, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 3) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard8, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 2) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard9, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 1) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard10, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = 2010 AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard11, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 + 1) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard12, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 10) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare1 , (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 9) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare2, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 8) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare3, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 7) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare4, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 6) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare5, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 5) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare6, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 4) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare7, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 3) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare8, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 2) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare9, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 1) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare10, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = 2010 AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare11, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 + 1) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare12, (SELECT yeard1 + yeare1 from prints as year1), (SELECT yeard2 + yeare2 from prints as year2), (SELECT yeard3 + yeare3 from prints as year3), (SELECT yeard4 + yeare4 from prints as year4), (SELECT yeard5 + yeare5 from prints as year5), (SELECT yeard6 + yeare6 from prints as year6), (SELECT yeard7 + yeare7 from prints as year7), (SELECT yeard8 + yeare8 from prints as year8), (SELECT yeard9 + yeare9 from prints as year9), (SELECT yeard10 + yeare10 from prints as year10), (SELECT yeard11 + yeare11 from prints as year12), (SELECT yeard12 + yeare12 from prints as year12) FROM prints GROUP BY year1, year2, year3, year4 ORDER BY prints.date_printed DESC LIMIT 0, 10 
[nativecode=1054 ** Unknown column 'yeard2' in 'field list']

Hi Rudy, I actually have everything working except a couple of things…

This “grand total” mixing currencies is the first…

Can I show you the table structures in a way that wont upset Sitepoint?

boy, what a coincidence, somebody else had another thread almost exactly like yours, and wouldn’t ya know, i came up with almost the identical query…

see http://www.sitepoint.com/forums/showpost.php?p=4653367&postcount=5

:slight_smile:

i had another look and came up with the following to get you on thr right track…

SELECT y.year
     , SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.print_key
                WHEN prints.currency = 'Euros'
                THEN prints.print_key * $euro
                ELSE NULL END)                
  FROM ( SELECT $year - 10 AS year
         UNION ALL
         SELECT $year - 9
         UNION ALL
         SELECT $year - 8
         UNION ALL
         SELECT $year - 7
         UNION ALL
         SELECT $year - 6
         UNION ALL
         SELECT $year - 5
         UNION ALL
         SELECT $year - 4
         UNION ALL
         SELECT $year - 3
         UNION ALL
         SELECT $year - 2
         UNION ALL
         SELECT $year - 1
         UNION ALL
         SELECT $year 
         UNION ALL
         SELECT $year + 1 ) AS y
LEFT OUTER
  JOIN prints
    ON YEAR(prints.date_printed) = y.year
   AND prints.sale_price > 0
GROUP
    BY y.year
SELECT yeard1 + yeard2 + yeard3 AS yeard_total
     , yeare1 + yeare2 + yeare3 AS yeare_total
  FROM ...

Well, I got so far, and realized I have a problem still… (mySQL question).

I have a nice grid summary of sales by euros by year, another summary of sales by dollars.

I have virtual fields yeard1, yeard2, yeard3 etc, and yeare1, yeare2, etc.

What’s the best way to add these fields for a total sales grid (again by year)…

Or you can use google

Actually, I remember I have an OANDA account :)…

I now have an xml feed and only need to figure out how to incorporate it…

tomorrow…

If you really wanted to get fancy you could use a web service to retrieve real-time conversion rates and build a dynamic query using that data in the application language. You would than have a self-updating real-time system for the conversion. That way no one needs to maintain a currency conversion table and everything will just work with real-time results all the time.

I’ve been moved! Teach me for posting at 5am…

Thanks for helping… Here’s the SHOW CREATE TABLE…

CREATE TABLE `prints` (
 `print_key` int(10) NOT NULL AUTO_INCREMENT,
 `edition_id` int(11) NOT NULL DEFAULT '0',
 `print_id` varchar(10) COLLATE utf8_bin DEFAULT NULL,
 `negative_id` varchar(25) COLLATE utf8_bin DEFAULT NULL,
 `date_printed` date DEFAULT NULL,
 `paper_type` varchar(50) COLLATE utf8_bin DEFAULT NULL,
 `status` varchar(25) COLLATE utf8_bin DEFAULT NULL,
 `date_of_disposition` date DEFAULT NULL,
 `frame_type` varchar(15) CHARACTER SET utf8 DEFAULT NULL COMMENT 'Examples: Wood, Metal',
 `frame_width` int(10) DEFAULT NULL,
 `frame_height` int(10) DEFAULT NULL,
 `frame_size_units` varchar(10) COLLATE utf8_bin DEFAULT '' COMMENT 'mm, cm, inches',
 `glass_type` varchar(25) COLLATE utf8_bin DEFAULT NULL,
 `studio_id` smallint(6) DEFAULT '0',
 `studio_location` varchar(50) COLLATE utf8_bin DEFAULT NULL,
 `contact_id` varchar(25) COLLATE utf8_bin DEFAULT NULL,
 `sale_price` int(10) DEFAULT NULL,
 `currency` varchar(255) COLLATE utf8_bin DEFAULT NULL,
 `notes` longtext COLLATE utf8_bin,
 `printNumber` varchar(50) COLLATE utf8_bin DEFAULT NULL,
 `uid` int(25) NOT NULL,
 `saleYear` varchar(50) COLLATE utf8_bin DEFAULT NULL,
 PRIMARY KEY (`print_key`),
 KEY `Prints_edition_serial_no_idx` (`edition_id`),
 KEY `Prints_customer_serial_no_idx` (`contact_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1850 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Do you mean the part about making a table?

yup – see the last half of post #26

:slight_smile:

the little chart helps a lot, as it shows what you need the query results for

what software will you be using to produce the chart?

also, how many currencies do you have?