Help with SQL2000 query please 1

[Webkins]

My table contains only 6 records. What I would like to do is combine (group) rows #1 and #2 into one line with the column Setups returned as a 2 instead of a 1. Only returning 5 rows of results, but I am having problems doing this. This is my query:

Select (convert(char(10), Setup, 101)) as Dates, Count(Setup) as Setups, Line from ttable_setups group by line, setup

These are the results:

Dates Setups Line
1 12/11/2008 1 456
2 12/11/2008 1 456
3 12/11/2008 1 451
4 12/12/2008 1 455
5 12/13/2008 1 454
6 12/14/2008 1 456

Please help if you can, I am frustrated and confused. Thank you so much.

 

[Webkins]

Sorry forgot to mention this. The table contains these columns:

Line, int
Setup, datetime

I would like to achieve the data like this:

Dates Setups Line
1 12/11/2008 47 456
2 12/11/2008 33 457
3 12/11/2008 12 458
4 12/11/2008 18 459
5 12/12/2008 11 456
6 12/12/2008 66 457
7 12/12/2008 27 458
8 12/12/2008 14 459
9 12/13/2008 34 456
10 12/14/2008 18 456

Thanks, I hope this helps

 

[tlbroadbent]

Try this.

SELECT
CONVERT(CHAR(10), Setup, 101) AS Dates,
COUNT(*) AS Setups,
Line
FROM ttable_setups
GROUP BY CONVERT(CHAR(10), Setup, 101), Line
ORDER BY 1,3

Terry L. Broadbent - DBA

 

[Webkins]

PERFECT, THANKS !!!!!!