Solved

Sum Columns WHERE?

Posted on 2010-11-12
7
487 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

These days socially coordinated efforts have turned into a critical requirement for enterprises.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

752 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