Group By with an Inner Join SQL Server

user_15038552 0
Viewed 51

Good morning Srs.

I have the following Sql Query below that joins data from two tables, the table Tab_Dados_Producao and Tab_Dados_Escolha

Code:

SELECT 

E3_GERAL.dbo.Tab_Dados_Producao.[E3TimeStamp] As 'Data',
E3_GERAL.dbo.Tab_Dados_Producao.Maquina As 'Maquina Linha',
E3_GERAL.dbo.Tab_Dados_Escolha.Maquina As 'Maquina Escolha'

FROM [E3_GERAL].[dbo].[Tab_Dados_Producao]

INNER JOIN [E3_GERAL].[dbo].[Tab_Dados_Escolha] ON [E3_GERAL].[dbo].[Tab_Dados_Producao].[E3TimeStamp]=[E3_GERAL].[dbo].[Tab_Dados_Escolha].[E3TimeStamp];

Result:

enter image description here

I would need to use the Group By clause in my SQL Query, to organize in the future by day, month and year, but I can't because an error message appears below, can you help me? appreciate

enter image description here

sql-server sql
1 Answers

If I understood you correctly then to organize the result you need order by instead of group by.

SELECT 

E3_GERAL.dbo.Tab_Dados_Producao.[E3TimeStamp] As 'Data',
E3_GERAL.dbo.Tab_Dados_Producao.Maquina As 'Maquina Linha',
E3_GERAL.dbo.Tab_Dados_Escolha.Maquina As 'Maquina Escolha'

FROM [E3_GERAL].[dbo].[Tab_Dados_Producao]

INNER JOIN [E3_GERAL].[dbo].[Tab_Dados_Escolha] ON [E3_GERAL].[dbo].[Tab_Dados_Producao].[E3TimeStamp]=[E3_GERAL].[dbo].[Tab_Dados_Escolha].[E3TimeStamp]

Order by E3_GERAL.dbo.Tab_Dados_Producao.[E3TimeStamp],
         E3_GERAL.dbo.Tab_Dados_Producao.Maquina,
         E3_GERAL.dbo.Tab_Dados_Escolha.Maquina ;

If you want to do some aggregation on the result; like counting rows etc you can use group by as below:

SELECT

E3_GERAL.dbo.Tab_Dados_Producao.[E3TimeStamp] As 'Data',
E3_GERAL.dbo.Tab_Dados_Producao.Maquina As 'Maquina Linha',
E3_GERAL.dbo.Tab_Dados_Escolha.Maquina As 'Maquina Escolha',count(*) row_counts

FROM [E3_GERAL].[dbo].[Tab_Dados_Producao]

INNER JOIN [E3_GERAL].[dbo].[Tab_Dados_Escolha] ON [E3_GERAL].[dbo].[Tab_Dados_Producao].[E3TimeStamp]=[E3_GERAL].[dbo].[Tab_Dados_Escolha].[E3TimeStamp]

Group by E3_GERAL.dbo.Tab_Dados_Producao.[E3TimeStamp],
         E3_GERAL.dbo.Tab_Dados_Producao.Maquina,
         E3_GERAL.dbo.Tab_Dados_Escolha.Maquina ;
user_86516010
Related