Solved

Sum Columns WHERE?

Posted on 2010-11-12
7
481 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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.

708 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

20 Experts available now in Live!

Get 1:1 Help Now