Remove'First' clause - 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

Other Forums

MS Access

Remove'First' clause

Author

Topic

javauser
Starting Member

3 Posts

Posted - 2008-08-13 : 13:56:51

The concerned query is the last one.And DQ1,DQ2 are the first and second queries

SELECT estats.ID, eparams.Parameter as [SC], eprms.ParamValue as [SrcCode]
FROM eparams INNER JOIN (eprms INNER JOIN estats ON eprms.ID = estats.ID) ON eparams.ParamID = eprms.ParamID
WHERE (((eparams.Parameter)="srcCode"))
ORDER BY estats.ID, eparams.Parameter, eprms.ParamValue;

SELECT estats.ID, eparams.Parameter as [Don], eprms.ParamValue as [DonAmt]
FROM eparams INNER JOIN (eprms INNER JOIN estats ON eprms.ID = estats.ID) ON eparams.ParamID = eprms.ParamID
WHERE (((eparams.Parameter)="donAmt"))
ORDER BY estats.ID, eparams.Parameter, eprms.ParamValue;


select  SC_NAMES.SC_Name  as [Source],first(DQ1.SrcCode) as [Source Code],count(DQ1.[estats.ID]) as [Number  of Donations],sum(DQ2.DonAmt) as [Donation Amount]
FROM (@Datalink.Query(1).Name@ DQ1 INNER JOIN @Datalink.Query(2).Name@ DQ2
ON  DQ1.id=DQ2.id) LEFT OUTER JOIN  SC_NAMES ON Mid(DQ1.SrcCode,1,2) = SC_NAMES.SC_CODE
Group By DQ1.SrcCode

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-13 : 14:15:49

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

javauser
Starting Member

3 Posts

Posted - 2008-08-13 : 14:58:48

Hi,

Sorry the information got cropped up.

The below sql query which works correctly.But it only displays the value 'source' only once eventhough when the same value appears more than once.How can I make it to print for each and every value.I tried to remove 'first' clause for source column an error occurs as

You tried to execute a query that does not include the specified expression 'Source' as part of an aggregate function.

The concerned query is the last one.And DQ1,DQ2 are the first and second queries

SELECT estats.ID, eparams.Parameter as [SC], eprms.ParamValue as [SrcCode]
FROM eparams INNER JOIN (eprms INNER JOIN estats ON eprms.ID = estats.ID) ON eparams.ParamID = eprms.ParamID
WHERE (((eparams.Parameter)="srcCode"))
ORDER BY estats.ID, eparams.Parameter, eprms.ParamValue;

SELECT estats.ID, eparams.Parameter as [Don], eprms.ParamValue as [DonAmt]
FROM eparams INNER JOIN (eprms INNER JOIN estats ON eprms.ID = estats.ID) ON eparams.ParamID = eprms.ParamID
WHERE (((eparams.Parameter)="donAmt"))
ORDER BY estats.ID, eparams.Parameter, eprms.ParamValue;


select  SC_NAMES.SC_Name  as [Source],first(DQ1.SrcCode) as [Source Code],count(DQ1.[estats.ID]) as [Number  of Donations],sum(DQ2.DonAmt) as [Donation Amount]
FROM (@Datalink.Query(1).Name@ DQ1 INNER JOIN @Datalink.Query(2).Name@ DQ2
ON  DQ1.id=DQ2.id) LEFT OUTER JOIN  SC_NAMES ON Mid(DQ1.SrcCode,1,2) = SC_NAMES.SC_CODE
Group By DQ1.SrcCode

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-13 : 15:19:55

You need to add SC_NAMES.SC_Name to the GROUP BY if you want to include that column in the results.

- Jeff
http://weblogs.sqlteam.com/JeffS

javauser
Starting Member

3 Posts

Posted - 2008-08-14 : 10:28:16

I have modified the query as below but it output remains same.Eventhough I tried using 'First' as first(SC_NAMES.SC_Name),first(DQ1.[SrcCode]) the output remains same let me know what more modification the query needs


select  (SC_NAMES.SC_Name) as [Source],first(DQ1.[SrcCode]) as [Source Code],count(DQ1.[estats.ID]) as [Number  of Donations],sum(DQ2.DonAmt) as [Donation Amount]
FROM (@Datalink.Query(1).Name@ DQ1 INNER JOIN @Datalink.Query(2).Name@ DQ2
ON  DQ1.id=DQ2.id) LEFT OUTER JOIN  SC_NAMES ON Mid(DQ1.SrcCode,1,2) = SC_NAMES.SC_CODE
Group By DQ1.SrcCode,SC_NAMES.SC_Name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources