Solved

Sum Columns WHERE?

Posted on 2010-11-12
7
486 Views
Last Modified: 2012-05-10
I need some help creating a query that pulls all the projects from my projects table,
then Sums all the bills charged to that project this week, & then sums all the bills charged to that project.  

here is picture of a simplified diagram showing what i need.

 Also I am using php 5.5, MYSQL 5.1, and adobe flash builder 4.

 Simple Diagram
0
Comment
Question by:cardilion
  • 3
  • 2
  • 2
7 Comments
 
LVL 4

Expert Comment

by:max-hb
ID: 34121820
This can easily be done using sub querys.
select
	projID,
	ProjName,
	(
		select count(*) from transactions T1
		where
		T1.projID = P.projID
	) as NumOfTransActions,
	(
		select sum(amount) from transactions T2
		where
		T2.projID = P.projID
		and
		(year(T2.timestamp) = year(Now())
		and
		(month(T2.timestamp) = month(Now())
		and
		(week(T2.timestamp) = week(Now())
	) as TransWeekly,
	(
		select sum(amount) from transactions T3
		where
		T3.projID = P.projID
	) as TransTotal
from projects P

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 34122199
you don't need subqueries there, try the below
select
	p.projID,
	p.ProjName,
	count(*) as CountofTransactions,
	sum(case when t.timestamp between now()-dayofweek(now())+1 and
					   now()+(7-dayofweek(now()))	
		then t.amount else 0 end
             ) as Transweekly,

	sum(t.amount) as transtotal
from projects P
inner join Transactions t on p.projid = t.projid
group by p.Projid, p.projName

Open in new window

0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 250 total points
ID: 34122219
actually you're better of with curdate() function
select
	p.projID,
	p.ProjName,
	count(*) as CountofTransactions,
	sum(case when t.timestamp between curdate()-dayofweek(curdate())+1 and
					   curdate()+(7-dayofweek(curdate()))	
		then t.amount else 0 end
             ) as Transweekly,

	sum(t.amount) as transtotal
from projects P
inner join Transactions t on p.projid = t.projid
group by p.Projid, p.projName

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cardilion
ID: 34122995
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as TransWeekly, ( select sum(amount) from transactions where transact' at line 19

I get this error when testing
0
 

Author Comment

by:cardilion
ID: 34123064
The total sum works and the count works, I am having trouble getting the weekly sum
0
 
LVL 4

Accepted Solution

by:
max-hb earned 250 total points
ID: 34123197
Sorry, dropped brackets when pasting.
select
	projID,
	ProjName,
	(
		select count(*) from transactions T1
		where
		T1.projID = P.projID
	) as NumOfTransActions,
	(
		select sum(amount) from transactions T2
		where
		(T2.projID = P.projID)
		and
		( year(T2.timestamp) = year(Now()) )
		and
		( month(T2.timestamp) = month(Now()) )
		and
		( week(T2.timestamp) = week(Now()) )
	) as TransWeekly,
	(
		select sum(amount) from transactions T3
		where
		T3.projID = P.projID
	) as TransTotal
from projects P

Open in new window

0
 

Author Comment

by:cardilion
ID: 34123272
Both worked so i split the points to both of ya'll.  Thank yall for the help, once again experts-exchange saves the day
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

685 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