Covert Oracle query to Sql Server query

Question

I am trying to run this query in SSMS the problem is i get returned the columns creation_date, transaction_type and RecordType which is fine, but as I run in my select query count(column_1) i get the following error

Column 'table_1.CREATION_DATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

DECLARE   @creation_date date
        , @transaction_type varchar
        , @record_type int
        , @column_1 varchar

select count(column_1) AS Volume
       ,JudgmentMonth=RIGHT(REPLACE(CONVERT(VARCHAR(7), creation_date, 103), '/', ''), 7)
       ,transaction_type, 
       REPLACE(record_type, 3, 2) as RecordType  
from   table_1 
where  transaction_type = 'PG' 
and    column_1 = 'CCJ'

Any Ideas on what i'm doing wrong, I am new to sql server and trying to convert this oracle query?

Many Thanks

Does Oracle really allow aggregate functions without a group by like that? I'm consistently surprised at Oracle "undocumented features". — Jacob H, May 30 '18 at 15:14
Did you google the error message? That should be your first step. — Tab Alleman, May 30 '18 at 15:15
Possible duplicate of [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) — Tab Alleman, May 30 '18 at 15:16
This isn't valid SQL, as in SQL the language. Are you *sure* this works in Oracle? What is `count(column_1)` supposed to show? The total count repeated on every row? Isn't that what the `OVER ()` clause is about? — Panagiotis Kanavos, May 30 '18 at 15:16
BTW are you absolutely sure you want to return `30052` for `2018-05-30`? That's what `RIGHT(REPLACE(CONVERT(VARCHAR(7), creation_date, 103), '/', ''), 7)` returns. — Panagiotis Kanavos, May 30 '18 at 15:36

score 2 · Answer 1 · answered May 30 '18 at 15:13

Count is an aggregate function, so you need to group by everything else:

DECLARE @creation_date DATE
      , @transaction_type VARCHAR
      , @record_type INT
      , @column_1 VARCHAR;

SELECT COUNT(column_1) AS Volume
     , RIGHT(REPLACE(CONVERT(VARCHAR(7), creation_date, 103), '/', ''), 7) AS JudgmentMonth
     , transaction_type
     , REPLACE(record_type, 3, 2) AS RecordType
FROM table_1
WHERE transaction_type = 'PG'
      AND column_1 = 'CCJ'
GROUP BY RIGHT(REPLACE(CONVERT(VARCHAR(7), creation_date, 103), '/', ''), 7)
       , transaction_type
       , REPLACE(record_type, 3, 2);

Thanks this worked I was trying to group by a single column instead of returning everything else and not the count column. — Vinnie S, May 30 '18 at 15:48

score 1 · Answer 2 · answered May 30 '18 at 15:45

You have a very arcane query. Try this:

select count(column_1) AS Volume, month(creation_date) as JudgmentMonth, transaction_type, 
       replace(record_type, '3', '2') as RecordType  
from table_1 
where transaction_type = 'PG' and column_1 = 'CCJ' 
group by month(creation_date), transaction_type, replace(record_type, '3', '2');

Notes:

month() is much more convenient than the complicated expression you are using (although it returns a number not a zero-padded string).
replace() is a string function, so you should pass string arguments.
you need the group by.
The count(column_1) is rather misleading. column_1 is never NULL because of the where clause, so I recommend count(*) or count(1) to emphasize that you are counting rows, not non-NULL values.

score 0 · Answer 3 · answered May 30 '18 at 15:26

0

This isn't valid SQL, as in SQL the language.

If you want to display the total result count repeated in all rows, the proper way in Oracle and SQL Server is to use the OVER clause and calculate it over a window/partition. Typcially, OVER contains a close that defines the results over which it applies (PARTITION BY). If the OVER clause is empty, it's calculated over all results.

You could rewrite your query to use OVER() to calculate and repeat the total count on all rows :

select count(column_1) OVER() AS Volume,
      ...
from   table_1 
...

You could also calculate the count per record type if you used the PARTITION BY record_type clause

select count(column_1) OVER(PARTITION BY record_type) AS Volume,
      ...
from   table_1 
...

answered May 30 '18 at 15:26

Panagiotis Kanavos

120,703
13
188
236

My Apologies this is the left out portion of the query above which works in Oracle `and trunc(creation_date) >= to_date(?1, 'DD/MM/YYYY') and trunc(creation_date) <= to_date(?2, 'DD/MM/YYYY') group by to_char(creation_date,'MM-YYYY'), REPLACE(record_type, 3, 2)` – Vinnie S May 30 '18 at 15:30
@VinnieS how can anyone help if you have one issue but ask about something completely different? Post the *full* query, not parts of it. BTW even in Oracle you should use date functions, not convert from and to strings. All those conversions and truncations mean that Oracle/SQL Server will have to scan the *entire* table to find and group matching entries – Panagiotis Kanavos May 30 '18 at 15:34
@VinnieS in both databases the trick is to *not* use functions. Use a calendar table/date dimension with extra indexed columns for month, year. Join witht he calendar table on the date field and group by the year, month columns of the calendar table. The server will pick *only* the matching rows in both databases – Panagiotis Kanavos May 30 '18 at 15:36
the reason for this is to generate a report based on a set of date parameters the (?1 and ?2) `trunc(@creation_date) >= to_date(?1, 'DD/MM/YYYY') and trunc(@creation_date) <= to_date(?2, 'DD/MM/YYYY')` the query fails in sql server as trunc and to_date is not recognized. – Vinnie S Jun 05 '18 at 15:58

Covert Oracle query to Sql Server query

3 Answers3