Solved

Sum Columns WHERE?

Posted on 2010-11-12
7
488 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

626 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