Solved

Sum Columns WHERE?

Posted on 2010-11-12
7
484 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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 …

831 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