Solved

Conditional Where clauses in MySQL Select Statement

Posted on 2009-05-07
3
519 Views
Last Modified: 2012-05-06
Hopefully this is an easy question. I have a MySQL database which houses sales data for my company. I need to generate a report for total sales for an accounts fiscal year which is July 1st - June 30th... The database breaks hold monthly totals SalesBal1 for Jan, SalesBal2 for Feb and so forth. But the data has to be fitered by the fiscal year in the database which is a calendar year.
     In the query below I can generate a report for July - Dec of 08, but I do not know how to then generate the Jan - June sales in 09... I know I could run a seperate query but I figure there is a simple way to combine this in one... Please let me know if my explanation is difficult to understand..
SELECT

  SUM(T.SalesBal7) As July_2008,

  SUM(T.SalesBal8) As Aug_2008,

  SUM(T.SalesBal9) As Sept_2008,

  SUM(T.SalesBal10) As Oct_2008,

  SUM(T.SalesBal11) As Nov_2008,

  SUM(T.SalesBal12) As Dec_2008

FROM arcpsale T

  Left Outer Join arcusts T1 On T.CustID = T1.CustID

WHERE T1.CustAcct ='MAINE130^'

  and T.FiscalYear =2008

Open in new window

0
Comment
Question by:dannyg280
  • 2
3 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24329089
One possibility is:
SELECT

  SUM(CASE WHEN T.FiscalYear = 2008 then T.SalesBal7 else 0 END) As July_2008,

  SUM(CASE WHEN T.FiscalYear = 2008 then T.SalesBal8 else 0 END) As Aug_2008,

  SUM(CASE WHEN T.FiscalYear = 2008 then T.SalesBal9 else 0 END) As Sept_2008,

  SUM(CASE WHEN T.FiscalYear = 2008 then T.SalesBal10 else 0 END) As Oct_2008,

  SUM(CASE WHEN T.FiscalYear = 2008 then T.SalesBal11 else 0 END) As Nov_2008,

  SUM(CASE WHEN T.FiscalYear = 2008 then T.SalesBal12 else 0 END) As Dec_2008,

  SUM(CASE WHEN T.FiscalYear = 2009 then T.SalesBal1 else 0 END) As Jan_2009,

  SUM(CASE WHEN T.FiscalYear = 2009 then T.SalesBal2 else 0 END) As Feb_2009,

  SUM(CASE WHEN T.FiscalYear = 2009 then T.SalesBal3 else 0 END) As Mar_2009,

  SUM(CASE WHEN T.FiscalYear = 2009 then T.SalesBal4 else 0 END) As Apr_2009,

  SUM(CASE WHEN T.FiscalYear = 2009 then T.SalesBal5 else 0 END) As May_2009,

  SUM(CASE WHEN T.FiscalYear = 2009 then T.SalesBal6 else 0 END) As Jun_2009
 

FROM arcpsale T

  Left Outer Join arcusts T1 On T.CustID = T1.CustID

WHERE T1.CustAcct ='MAINE130^'

  and T.FiscalYear BETWEEN 2008 AND 2009

Open in new window

0
 

Author Comment

by:dannyg280
ID: 24329193
Thank you for the responce. I'm now getting a syntext error "Right parenthese expected after aggregate SUM function argument, instead found 'WHEN' in source column expression.

Is there a syntext error?
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 24331083
what abou this?
SELECT

  SUM(CASE T.FiscalYear WHEN 2008 then T.SalesBal7 else 0 END) As July_2008,

  SUM(CASE T.FiscalYear WHEN 2008 then T.SalesBal8 else 0 END) As Aug_2008,

  SUM(CASE T.FiscalYear WHEN 2008 then T.SalesBal9 else 0 END) As Sept_2008,

  SUM(CASE T.FiscalYear WHEN 2008 then T.SalesBal10 else 0 END) As Oct_2008,

  SUM(CASE T.FiscalYear WHEN 2008 then T.SalesBal11 else 0 END) As Nov_2008,

  SUM(CASE T.FiscalYear WHEN 2008 then T.SalesBal12 else 0 END) As Dec_2008,

  SUM(CASE T.FiscalYear WHEN 2009 then T.SalesBal1 else 0 END) As Jan_2009,

  SUM(CASE T.FiscalYear WHEN 2009 then T.SalesBal2 else 0 END) As Feb_2009,

  SUM(CASE T.FiscalYear WHEN 2009 then T.SalesBal3 else 0 END) As Mar_2009,

  SUM(CASE T.FiscalYear WHEN 2009 then T.SalesBal4 else 0 END) As Apr_2009,

  SUM(CASE T.FiscalYear WHEN 2009 then T.SalesBal5 else 0 END) As May_2009,

  SUM(CASE T.FiscalYear WHEN 2009 then T.SalesBal6 else 0 END) As Jun_2009

 

FROM arcpsale T

  Left Outer Join arcusts T1 On T.CustID = T1.CustID

WHERE T1.CustAcct ='MAINE130^'

  and T.FiscalYear BETWEEN 2008 AND 2009

Open in new window

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now