Any way to SELECT only DISTINCT Substring ? 1

[ljsmith91]

I am trying to do a SELECT and SUM up a column.

Example of the input table columns are:

Server backupended gbytes backed up
------- ------------------- -----------------
server1 Apr 10, 2005 10:00 10.05
server2 Apr 10, 2005 14:02 1.41
server3 Apr 10, 2005 17:58 22.03
server2 Apr 11, 2005 04:00 6.08
server2 Apr 11, 2005 22:00 0.06

I want to total all gbytes for each day regardless of the server name. So I do not care about the time in the backupended field but I do care about the day. So I want to use SUBSTRING(backupended,1,12) and somehow use DISTINCT to only summarize the gbuytes backed up that day but I cannot get DISTINCT to work with the SUBSTRING keyword.

Here is my not working attempt. I am only a rookie and looking for any guidance. It tells me I have incorrect syntax near DISTINCT. I have tried several variations.

SELECT
server,
SUM(gbytes) as Gigabytes,
DISTINCT SUBSTRING(backupended,1,12)
from
backup_jobs
GROUP BY
server, backupended
ORDER BY
backupended





I need to be able to SUM up a substring of a date time field.

 

[NoCoolHandle]

Its your group by and order by that was biting you..

i.e.
SELECT
server,
SUM(gbytes) as Gigabytes,
SUBSTRING(backupended,1,12)
from
backup_jobs
GROUP BY
server, [red]SUBSTRING(backupended,1,12)[\red]
ORDER BY
[\red] SUBSTRING(backupended,1,12)[red]

 

[NoCoolHandle]

oops wrong slash

try
.
SELECT
server,
SUM(gbytes) as Gigabytes,
[red] SUBSTRING(backupended,1,12) as BackupDay[/red]
from
backup_jobs
GROUP BY
server, [red]SUBSTRING(backupended,1,12)[/red]
ORDER BY
[red] SUBSTRING(backupended,1,12)[/red]

 

[ljsmith91]

That did the trick. Thanks so much. So much still to learn. I appreciate the help.

LJS