Query For Report...HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query For Report...HELP

Author

Topic

kuruption213
Starting Member

4 Posts

Posted - 2005-08-02 : 16:18:44

This is a pretty basic request but I seem to be having problems with it. I'm just trying to make a report that returns results for an application and its many or 0 sponsers.

For example
Title Sponsers Total
Application A 1500
Application B 1000
Application C 0

Application A may have had 3 sponsers eaching giving 500 a piece. While Application B could have had 3 sponsers giving 100, 600, and 300. Then there is Application C who has 0.

This is the query I'm using

SELECT dbo.Activities.AbbrevTitle,
(SELECT SUM(dbo.PAFundingCompanies.PAFCAmt) AS Expr1, dbo.PAFundingCompanies.PAID
FROM dbo.PAFundingCompanies INNER JOIN
dbo.PostActivities ON dbo.PAFundingCompanies.PAID = dbo.PostActivities.PAID INNER JOIN
dbo.Activities ON dbo.PostActivities.ActivityID = dbo.Activities.ActivityID
WHERE (dbo.PAFundingCompanies.FCTypeID = 1)
GROUP BY dbo.PAFundingCompanies.PAID) AS FUNDINGCOMPANYTOTAL
FROM dbo.Activities INNER JOIN
dbo.PostActivities ON dbo.Activities.ActivityID = dbo.PostActivities.ActivityID
WHERE (dbo.Activities.StatusID = 10)

I'm getting the error "only one expression can be specified in the select list when the subquery is not introduced with EXISTS" If someone can help plese help me out.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-02 : 16:34:09

Hey welcome to the forum.

Can you please post the table structures and some sample data / desired results? This will help you get an answer ASAP.

Look here for some guidelines:
[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Nathan Skerl

kuruption213
Starting Member

4 Posts

Posted - 2005-08-02 : 16:43:44

Sure here are the 3 tables

Activities
-----------
- ActivityID
- AbbrevTitle (Title)
- Status

PostActivities
-----------
- PAID
- ActivityID (Foreign Key - An Activity once having a status of approved (10) will get a Post Activity)

PAFundingCompanies (The companies that are associated with sponsering the Post Activity)
----------------
- PAFCID - Unique id of a sponser
- PAID - Foreign Key
- PAFCName - Name of sponser
- PAFCAmt - Amt sponer has contributed to a Post Activity

kuruption213
Starting Member

4 Posts

Posted - 2005-08-02 : 16:59:57

Sample data

Activities
-----------
-------- ActivityID ------------ AbbrevTitle ------------------ StatusID
-----------1--------------------My New Activity------------------10---
-----------2--------------------My 2nd Activity------------------10---
-----------3--------------------My New Activity-------------------3---
PostActivities
-----------
----------PAID------------------ ActivityID
------------1----------------------1-----
------------2----------------------2------

PAFundingCompanies (NOTE THE PAFCID is just there to uniquely identify a row, not a specific company, the same company may appear many times duplicated)
--------------------
------PAFCID---------------PAID-------------PAFCName---------------PAFCAmt
-------1--------------------1--------------Company A---------------1000
-------2--------------------1--------------Company B---------------5000
-------3--------------------2--------------Company C---------------3000
-------4--------------------2--------------Company D---------------5000
-------5--------------------2--------------Company E---------------2000

Desired Results
AbbrevTitle ------------------FUNDINGCOMPANYTOTAL
My New Activity -----------------6000
My 2nd Activity------------------10000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-02 : 17:03:23

kuruption123, please read the link again to see how to post this information. Here's another link that's similar. Check out the link in the first post as well to see the format. The link below explains and shows why we need it in that format.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090

Tara

kuruption213
Starting Member

4 Posts

Posted - 2005-08-02 : 17:31:45

thanks anyways i got it....here is the query in case anyone has the same type of problem....

SELECT dbo.PostActivities.PAID, dbo.Activities.AbbrevTitle, dbo.Activities.StartDate, dbo.Activities.EndDate, dbo.PostActivities.NumCredits,
dbo.PostActivities.NumPhys, dbo.PostActivities.NumNonPhys, SUM(dbo.PAFundingCompanies.PAFCAmt) AS TOTALFUNDINGCOMPANY,
dbo.PAFundingCompanies.FCTypeID
FROM dbo.Activities INNER JOIN
dbo.PostActivities ON dbo.Activities.ActivityID = dbo.PostActivities.ActivityID INNER JOIN
dbo.PAFundingCompanies ON dbo.PostActivities.PAID = dbo.PAFundingCompanies.PAID
WHERE (dbo.Activities.StatusID = 10) AND (dbo.PAFundingCompanies.PAID IN
(SELECT dbo.PAFundingCompanies.PAID
FROM dbo.PAFundingCompanies INNER JOIN
dbo.PostActivities ON dbo.PAFundingCompanies.PAID = dbo.PostActivities.PAID INNER JOIN
dbo.Activities ON dbo.PostActivities.ActivityID = dbo.Activities.ActivityID
GROUP BY dbo.PAFundingCompanies.PAID, dbo.PAFundingCompanies.FCTypeID
HAVING (dbo.PAFundingCompanies.FCTypeID = 1))) AND (dbo.PAFundingCompanies.FCTypeID = 1)
GROUP BY dbo.Activities.AbbrevTitle, dbo.PostActivities.PAID, dbo.Activities.StartDate, dbo.Activities.EndDate, dbo.PostActivities.NumCredits,
dbo.PostActivities.NumPhys, dbo.PostActivities.NumNonPhys, dbo.PAFundingCompanies.FCTypeID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources