how do i fix this sp? - 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

SQL Server Development (2000)

how do i fix this sp?

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-08-06 : 17:04:54

select distinct dteCycleDate, txtCompanyNum, blnLocked
from dbo.CycleDateInfo
where (txtInternetNum = '11') and (dteCycleDate is not null) and (dteCycleDate != '01/01/1999')
group by dteCycleDate,txtCompanyNum
order by dteCycleDate desc

Adding the blnLocked field in the select clause is now giving me this error: "Column 'dbo.CycleDateInfo.blnLocked' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.". Adding the blnLocked filed in the group by gives me this error: "Cannot group by a bit column.".

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-06 : 17:11:38

Not sure if this can be done...maybe I can learn too:

select distinct dteCycleDate, txtCompanyNum, blnLocked = case blnLocked when 1 then 1 else 0
from dbo.CycleDateInfo
where (txtInternetNum = '11') and (dteCycleDate is not null) and (dteCycleDate != '01/01/1999')
group by dteCycleDate,txtCompanyNum,blnLocked
order by dteCycleDate desc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-06 : 17:30:55

We need to see some sample data and what the expected result set would be.

Tara

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-06 : 18:05:23

If at first you don't succeed, cheat:

select distinct dteCycleDate, txtCompanyNum, CAST(blnLocked AS TINYINT)
   FROM dbo.CycleDateInfo
   WHERE (txtInternetNum = '11')
      AND (dteCycleDate is not null)
      AND (dteCycleDate != '01/01/1999')
   GROUP BY dteCycleDate, txtCompanyNum, CAST(blnLocked AS TINYINT)
   ORDER BY dteCycleDate desc

-PatP

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-06 : 23:08:57

Nice Pat...looks and feels better than mine

- RoLY roLLs

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-09-22 : 12:51:26

Do I really need to have all the fields in the select clause part of my group by clause? What if I am selecting 15 fields, then all these should be in my group by clause?

Is there another way of doing a group by?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-22 : 12:53:27

It just depends on what you are trying to do. Show us an example.

Tara

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-09-22 : 13:00:27

My query is pretty similar to what I posted earlier, except that there are more fields:

SELECT @total as total, ID, lngIndex, dteCycleDate, dteTransacDate, txtMerchantName, curTransacAmnt, txtMemoFlag, blnSent, curOrigAmnt, txtAccountNum, curForeignAmnt, txtCompanyNum, blnL3,
countL3, txtCostCenter, txtStatus, blnLocked,
MoreRecords =
(
SELECT COUNT(*)
FROM #TransTemp TI
WHERE TI.ID >= @LastRec
)
FROM #TransTemp
WHERE ID > @FirstRec AND ID < @LastRec
GROUP BY txtCostCenter

I get the error "[field] is invalid in the select list because it is not contained in either an aggregate function or the Group by clause".

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-22 : 13:07:01

So which rows do you want to display for these extra columns? The MAX, the MIN, SUM, ...? You are grouping by txtCostCenter but not the rest, so if there are multiple rows for that txtCostCenter, then SQL needs to know which one to display. If there is only one, then you can join your query (minus the extra columns) as a derived table.

Tara

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-09-22 : 13:16:50

This is getting interesting Tara. I think I'm on the verge of learning a different way of grouping by.

Could you pls rewrite my query using "join your query (minus the extra columns) as a derived table"? I want all the fields in my select clause to display with all records of the same txtcostcenter grouped together.

Thanks for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-22 : 13:20:06

Here's an example:

SELECT t2.Column1, t2.Column2, t1.Column3, t1.Column4
FROM Table1 t1
INNER JOIN (SELECT Column1, MAX(Column2) AS Column2 FROM Table1 GROUP BY Column1) t2
ON t1.Column1 = t2.Column1 AND t1.Column2 = t2.Column2

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources