Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Sum Columns WHERE?

Posted on 2010-11-12
Medium Priority
489 Views
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.

0
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
• 3
• 2
• 2

LVL 4

Expert Comment

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
``````
0

LVL 41

Expert Comment

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
``````
0

LVL 41

Assisted Solution

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
``````
0

Author Comment

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

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

LVL 4

Accepted Solution

max-hb earned 1000 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
``````
0

Author Comment

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

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
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.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastrâ€¦
###### Suggested Courses
Course of the Month9 days, 1 hour left to enroll