CODEWITHSUNDEEP
sqlsql-serverdatetimeaggregation
morteza kavakebi
morteza kavakebi

Reputation: 1680

SQL Server : group by a function on a field an select that

I have a group by YEAR(mydate),MONTH(mydate)

I want to select them as a unique field like

SELECT SUM(Price),YEAR(mydate) + '/' + MONTH(mydate)
FROM MyTable
GROUP BY YEAR(mydate), MONTH(mydate)

or something like this.

mydate is smalldatetime.

Is it possible?

Upvotes: 2

Views: 29260

Answers (5)

Harsh Naidu
Harsh Naidu

Reputation: 1

SELECT CAST(DATENAME(yy, mydate) AS CHAR(4)) + '-' + CAST(DATENAME(mm, mydate) AS VARCHAR(2)) 'Date'
 ,SUM(Price)
FROM [My Table]
GROUP BY DATENAME(yy, mydate)
 ,DATENAME(ww, mydate)

Upvotes: -1

Stefano Pedone
Stefano Pedone

Reputation: 25

here my solution :) I need to get older date in a sqlserver table (columns year_rif and month_rif) to insert newer record from a linked oracle table (to mirroring oracle table in sqlserver by monthly job)

DECLARE @anno_INIZIO int
DECLARE @mese_INIZIO int

select top(1) @mese_INIZIO= mese_rif,@anno_INIZIO=anno_rif 
 from dbo.ORACLE_BUDGET_MENSILI 
 group by  mese_rif,anno_rif 
 order by max(mese_rif/12+anno_rif) desc

set @anno_inizio=coalesce(@anno_INIZIO ,2016)
set @mese_INIZIO=coalesce(@mese_INIZIO,1)

At first import I assume start get data from january 2016 The full job is:

    DECLARE @anno_INIZIO int
    DECLARE @mese_INIZIO int
--get max year/mont avaible in mirrored table
    select top(1) @mese_INIZIO= mese_rif,@anno_INIZIO=anno_rif from dbo.ORACLE_BUDGET_MENSILI group by  mese_rif,anno_rif order by max(mese_rif/12+anno_rif) desc
--
    set @anno_inizio=coalesce(@anno_inizio,2016)
    set @mese_INIZIO=coalesce(@mese_INIZIO,1)
-- query to linked oracle to get into table newer data
    SET @SQL='select * FROM OPENQUERY(ADL_REP,''
    SELECT CODICE_UO,ANNO_RIF,MESE_RIF,ID_CLASSE,BUDGET,ORE_MENSILI_OBIETTIVO FROM ADL.BUDGET_MENSILI where  mese_rif/12+anno_rif > '+convert(varchar(255),@mese_INIZIO/12+@anno_INIZIO)+'
    '')'
    INSERT INTO uat.[dbo].ORACLE_BUDGET_MENSILI EXECUTE sp_executesql @SQL

Upvotes: 0

user1134181
user1134181

Reputation:

For MS SQL Server 2012, with leading zeros:

CAST(YEAR(mydate) AS VARCHAR(4)) + '/' +
RIGHT('00' + CAST(MONTH(mydate) AS VARCHAR(2)), 2)

Upvotes: 0

podiluska
podiluska

Reputation: 51494

You need to convert them to strings

Try

SELECT convert(varchar(7), mydate ,111)
FROM MyTable 
GROUP BY convert(varchar(7), mydate ,111)

Upvotes: 2

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

SELECT cast(YEAR(mydate) as CHAR(4))+'/'+cast(MONTH(mydate) as varchar(2))
FROM MyTable
Group by YEAR(mydate),MONTH(mydate)

Upvotes: 7

Related Questions