Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1093
  • Last Modified:

MySQL Sub-query for YTD

SubQuery.txtExperts,

I need to add a YTD column for the attached query.  

The idea is to display values for each month, but to have the sum of expenditures for the year in YTD.  Thus, the WHERE clause at the query's end does not apply to the YTD column.

So, could someone please show me:

1.  How to add a sub-query within this sql statement with the appropriate WHERE criterion to display YTD values.

2.  An easy way in either PHP or MySQL to retrieve the first day of the year in question.

Your help is much appreciated.
0
Glen Gibb
Asked:
Glen Gibb
  • 5
  • 3
2 Solutions
 
Ray PaseurCommented:
In PHP, the first day of the year (in ISO-8601 format) is date('Y-01-01').  This article explains a bit more about it.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
Glen GibbOwnerAuthor Commented:
You're always the Man, Ray!

Tnx for the quick response.

But does this formula extract the year of '2013-03-31' so I can make my new value '2013-01-01'?

And can someone show me how to insert my SELECT SUM(delivery_volume * price) WHERE open_date BETWEEN '2013-01-01' AND 'current_date'?
0
 
Ray PaseurCommented:
...formula extract the year of '2013-03-31' so I can make my new value '2013-01-01'?
I think so, if it is applied correctly.  I can't answer all of the question since I have to watch basketball with houseguests, but almost any English-language human-readable date string can be converted to a timestamp with strtotime() and any timestamp can be converted to a human-readable date with date().
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Glen GibbOwnerAuthor Commented:
Tnx.  I understand the burden March Madness can impose!  I'll wait for the SQL.
0
 
Ray PaseurCommented:
Pretty sure someone else can drop the SQL for you.  Fing Duke cost me a billion this year!
0
 
Glen GibbOwnerAuthor Commented:
Goes to show even a Savant can't necessarily predict the future!  But I certainly respect your knowledge in this arena.
0
 
PortletPaulfreelancerCommented:
Your current query does not identify tables with each field in the select clause, so it's not clear what tables are required for a subquery:

SELECT SUM( ???.delivery_volume * ???.price)
FROM ????  
WHERE ???.??? = ???.???
AND open_date BETWEEN '2013-01-01' AND 'current_date'?
0
 
Glen GibbOwnerAuthor Commented:
Tnx, P...Paul, for weighing in as well.  The whole query is included in the file at the top of the page.  

You are right, however, in requiring the tables from which the values are to be selected.  That isn't at all clear from my post -- but I had sort of hoped it would be from the info in the file.

However, I've figured out how to include the SubQuery as a column:  just put it in parentheses > 

(SELECT SUM (...) FROM tbl WHERE open_date BETWEEN 'y-01-01' AND 'curr') AS YTD

That can be added to the query shown in the file w/out producing an error.

I think I can now compose the query with the info you experts have supplied.
0
 
Glen GibbOwnerAuthor Commented:
Tnx, Experts.  I've got what I needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now