How do I use Group By based on a Case statement in Oracle?
select
(case
when exp_date > sysdate then 1
when exp_date <= sysdate then 2
else 3
end) expired, count(*)
from mytable
group by (case
when exp_date > sysdate then 1
when exp_date <= sysdate then 2
else 3
end)
oracle - case statement and group by
Oracle doesn't support column aliases in the group by clause. Use a subquery:
SELECT SALES_ID_CO, SALES_BRANCH,SALES_DATE, COUNT(*)
FROM (Select sales_date,
(CASE WHEN sales_id like '1%'
THEN substr(sales_id,2,6)
WHEN sales_id like '3%'
THEN substr(sales_id ,2,6)
WHEN sales_id like '4%'
THEN substr(sales_id ,2,6)
WHEN sales_id like '5%'
THEN substr(sales_id ,2,6)
WHEN sales_id like '9%'
THEN substr(sales_id ,1,6)
WHEN sales_id like '70%' OR sales_id like '90%' OR sales_id like '31%'
END) AS SALES_ID_CO,
(case WHEN sales_branchlike '10%' AND sales_code<> '678 '
THEN substr(sales_branch,2,6)
WHEN sales_branchlike '200%' AND sales_code<> '678 '
THEN substr(sales_branch,2,6)
WHEN sales_branchlike '300%' AND sales_code<> '678 '
THEN substr(sales_branch,2,6)
WHEN sales_branchlike '456%' AND sales_code<> '678 '
THEN substr(sales_branch,2,6)
END) AS SALES_BRANCH
from tbl_sales_cde
where sales_country IN ('USA', 'ASIA', 'EU')
) s
group by SALES_ID_CO, SALES_BRANCH,SALES_DATE;
Note: You also had curly single quotes in the where clause, but that might be a copying issue.
SQL Group By with Case
If you want a group by display_name you should repeat the same condition in group by
SELECT
CASE
WHEN aoi.is_animal = 'N' THEN TO_CHAR(aot.name)
ELSE 'Boarding Charges'
END AS display_name,
aoi.is_animal,
CASE
WHEN aot.name = 'Misc.' THEN 'Y'
ELSE 'N'
END AS show_details,
SUM(aoi.quantity * aoi.unit_price) as total
FROM ANIMAL_ORDER ao
LEFT JOIN ANIMAL_ORDER_ITEM aoi ON aoi.order_id = ao.id
LEFT JOIN ANIMAL_ORDER_TYPE aot ON aot.id = aoi.order_type_id
WHERE ao.order_stage != 'CANCELLED'
GROUP BY
CASE
WHEN aoi.is_animal = 'N' THEN TO_CHAR(aot.name)
ELSE 'Boarding Charges'
END ,
aoi.is_animal,
CASE
WHEN aot.name = 'Misc.' THEN 'Y'
ELSE 'N'
END
Group by expression with case statement in oracle?
Columns that aren't aggregated should be part of the GROUP BY clause. It means that "solution"
- isn't to remove the 2nd
CASEfromSELECT, but to - include it into
GROUP BY
Something like this (CTE is here just to have some sample data in order to show that query works; does it produce what you meant it to, I can't tell):
SQL> WITH mytable (
2 country_code, cust_usr_id, facility_id, account_no, account_class
3 ) AS
4 (SELECT 1, 1, 1, 1, 1 FROM dual UNION ALL
5 SELECT 1, 2, 3, 4, 5 FROM DUAL
6 )
7 SELECT country_code,
8 cust_usr_id,
9 CASE
10 WHEN facility_id IS NOT NULL THEN
11 facility_id
12 WHEN account_no IS NOT NULL THEN
13 account_class
14 END acc_fa_id,
15 COUNT(1),
16 --
17 CASE
18 WHEN facility_id IS NOT NULL THEN
19 'FACILITY_ID'
20 ELSE
21 'ACCOUNT_CLASS'
22 END identifiers
23 FROM mytable
24 WHERE ( facility_id IS NOT NULL
25 OR account_no IS NOT NULL)
26 GROUP BY country_code,
27 cust_usr_id,
28 CASE
29 WHEN facility_id IS NOT NULL THEN
30 facility_id
31 WHEN account_no IS NOT NULL THEN
32 account_class
33 END,
34 CASE
35 WHEN facility_id IS NOT NULL THEN
36 'FACILITY_ID'
37 ELSE
38 'ACCOUNT_CLASS'
39 END;
COUNTRY_CODE CUST_USR_ID ACC_FA_ID COUNT(1) IDENTIFIERS
------------ ----------- ---------- ---------- -------------
1 2 3 1 FACILITY_ID
1 1 1 1 FACILITY_ID
SQL>
Use GROUP BY with SELECT CASE WHEN statement
I think you want to phrase this with the CASE as an argument to the SUM():
SELECT ORDER_ID,
SUM(CASE WHEN ORDER_DATE BETWEEN '2020-07-01' AND '2020-12-31'
THEN ORDER_AMOUNT * 1.16
ELSE ORDER_AMOUNT * 1.19
END) AS gross_amount
FROM my_dwh_table
GROUP BY ORDER_ID;
No ORDER BY is needed.
Note: This assumes that ORDER_DATE is never NULL (hence no need for an ELSE 0 and that '2020-07-01' is a valid date on your system. I would normally use DATE '2020-07-01' for a date constant.
Oracle SQL - Invalid GROUP BY when using CASE WHEN statement
You need the case expression inside the aggregate, not the other way round:
SELECT
PART_NUM,
PART_NAME,
PART_SERIAL_NUM,
SUM(REPAIR_HOURS) AS "TOTAL_REPAIR_HOURS",
SUM(CASE WHEN REPAIR_TYPE = 'A' THEN REPAIR_HOURS ELSE 0 END) AS "CONCRETE_HOURS",
SUM(CASE WHEN REPAIR_TYPE = 'B' THEN REPAIR_HOURS ELSE 0 END) AS "DEMOLITION_HOURS",
SUM(CASE WHEN REPAIR_TYPE = 'C' THEN REPAIR_HOURS ELSE 0 END) AS "AUTOMOTIVE_HOURS"
FROM
PARTS
LEFT JOIN REPAIRS
ON PART_NUM = REPAIRED_PART AND
PART_SERIAL_NUM = REPAIRED_PART_SERIAL_NUM
WHERE
PART_NUM = '500ABX'
GROUP BY
PART_NUM,
PART_NAME,
PART_SERIAL_NUM
SQL query using a case statement within the group by fields
I think the error is you are describing a FIELD (ie: result column) for the query like the others: DMAGATR.WRK_LOC_LEVEL4 ,DMBR.WRK_LOC_NM ,DMBR.RELCD ,COUNT (DISTINCT DMBR.DMBRKEY...
I think the error is that when using a SQL-Select statement for a resulting COLUMN, it must only return a single row. Since your query is just "... FROM DMBR ) as CMPN", you are returning more than one row for the field and no Database knows how to guess your result.
What you are probably missing is both a WHERE clause on the field, and possibly a GROUP by if you are looking for a distinct value from within the DMBR table.
Fix that and it should get you MUCH further along. Not knowing the rest of data structure or relationships, I can't figure what your ultimate result is meant to be.
ADDITIONAL COMMENT...
By looking at other answers provided, they have offered to do an immediate CASE WHEN on whatever the current "DMBR" record you are on, which would be correct, but not quite working. I think due to the two possible results, that too will have to be part of the group by.. as count(DISTINCT), the group by has to be based on any non-aggregation columns... of which, this case/when would be as such.. So your ultimate result would have
Lvl, Work Loc, RelCD, Case/when, count(distinct) where...
SEG 1 999
Other 999
Additionally, your CASE/WHEN had two components exactly matching your WHERE clause, so I took it out of there since no records of that set would have been returned anyway.
So, all that being said, I would write it as...
SELECT
DMAGATR.WRK_LOC_LEVEL4,
DMBR.WRK_LOC_NM,
DMBR.RELCD,
CASE WHEN (DMBR.WRK_LOC_NM = '6'
AND DMBR.GDR = 'M' )
THEN 'SEG 1'
ELSE 'OTHER'
END as WhenStatus,
COUNT (DISTINCT DMBR.DMBRKEY) AS ELIG_COUNT
FROM
DMBR
JOIN DCUST
ON DMBR.DCUSTKEY = DCUST.DCUSTKEY
JOIN DMAGATR
ON DMBR.DMBRKEY = DMAGATR.DMBRKEY
LEFT JOIN DMDYNATR
ON DMBR.DMBRKEY = DMDYNATR.DMBRKEY
WHERE
DMBR.C_TIMESSTAMP <= '12/31/2011'
AND DMBR.REL_CD in ('0','1')
AND DMBR.EE_STS IN ('A','L'))
AND DCUST.PRCD = 'TAR'
AND ( DMBR.DEL_DT IS NULL
OR DMBR.DEL_DT > '12/31/2011')
GROUP BY
DMAGATR.WRK_LOC_LEVEL4,
DMBR.WRK_LOC_NM,
D_MEMBER.REL_CD,
CASE WHEN (DMBR.WRK_LOC_NM = '6'
AND DMBR.GDR = 'M' )
THEN 'SEG 1'
ELSE 'OTHER'
END
Finally, sometimes, I've seen where a group by will choke on a complex column, such as a case / when. However, different servers allow ordinal reference to the group by (and order by too) positions. So, since the query has 4 non-aggregate columns (all listed first), then the count of distinct, you MIGHT be able to get away with changing the GROUP BY clause to...
GROUP BY 1, 2, 3, 4
All pertaining to the sequential order of columns STARTING the SQL-Select call.
--- CLARIFICATION about group by and case-sensitivity
First, the case-sensitivity, most engines are case-sensitive on keywords, hence CASE WHEN ... AND ... THEN ... ELSE ... END.
As for the "group by" (and also works for the "order by"), its more of a shortcut to the ordinal columns in your query instead of explicitly listing the long names to them and having to re-type the entire CASE construct a second time, you can just let the engine know which column of the result set you want to order by look at the following (unrelated) query...
select
lastname,
firstname,
sum( orderAmount ) TotalOrders
from
customerOrders
group by
lastname,
firstname
order by
TotalOrders DESC
and
select
lastname,
firstname,
sum( orderAmount ) TotalOrders
from
customerOrders
group by
1,
2
order by
3 DESC
Each would produce the same results... The fictitious customerOrders table would be pre-aggregated by last name and first name and show the total per person (all assuming no duplicate names for this example, otherwise, I would have used a customer ID). Once that is done, the order by kicks in and will put in order of the most sales to a given customer in DESCENDING order at the top of the list.
The numbers just represent the ordinal columns being returned in the query instead of long-hand typing the field names. More for the issue you have of your "CASE/WHEN" clause to prevent oops retyping and missing it up in the group by and pulling your hair out figuring out why.
How to use case statement and min() with group by?
You have to use analytical MIN() function like the below without group by
SELECT distinct CASE WHEN spd.IS_MAIN_DEFECT='Y'
THEN spd.piece_Defect_num_id
ELSE min(spd.PIECE_DEFECT_NUM_ID) over () END AS defect
FROM piece P , STY_PIECE_DEFECT spd ,STY_DEFECT_CATALOGUE sdc ,piece_history ph
, piece_history_out pho, PLANT_CONFIG pc
(...inner join and where clause)
GROUP BY with nested case expression - is there better way?
When the values are calculated directly from the row I tend to use cross apply for this as it is more concise than adding a derived table/CTE whose only purpose is to define a column alias but still needs to project out the remaining columns and contain a FROM.
This is not an option for expressions that reference window functions or aggregate functions but will work fine here.
select mp.professionals
,ca.age -- straight forward
,case ca.age -- nested case
when 'New' then sum(fees) * 0.5
when 'Old' then sum(fees) * 0.25
else 0
end Credit
,case ca.age -- nested case
when 'New' then 'Welcome!'
when 'Old' then 'Thank you for being a long-time Client!'
end Greeting
from mattersprofessionals mp
inner join matters m on m.matters = mp.matters
inner join stmnledger sl on sl.matters = mp.matters
cross apply (select case when sl.stmndate < dateadd(year, 3, m.qClientOpenDate) then 'New' else 'Old' end) ca(age)
group by mp.professionals, ca.age
Complex case statement using group
You have a few issues with your query.
You don't
endyourcase.You
group byvaccine_day which will return you each vaccine_day.
Didn't test it as I don't have the full sample data, both your sample data are the same id and days, but you can start with something like this:
select id, case when vaccine_code like '%1A%' then 'dose1'
when vaccine_code like '%2A%' then 'dose2'
when vaccine_code = 'moderna' and vaccine_day = min(vaccine_day) then 'dose1'
when vaccine_code = 'moderna' and vaccine_day = max(vaccine_day) then 'dose1'
end as vaccine_dose
from tbl
group by id, vaccine_code;
Related Topics
Using a SQL Server for Application Logging. Pros/Cons
How to Use Left & Right Functions in SQL to Get Last 3 Characters
Tools to Work with Stored Procedures in Oracle, in a Team
Referencing a Composite Primary Key
Formatting Clear and Readable SQL Queries
Joining Multiple Common Table Expressions
Postgres: Select All Row with Count of a Field Greater Than 1
What Is The Max Size of Varchar2 in Pl/Sql and Sql
Insert-Output Including Column from Other Table
How to Use SQL Server Stored Procedures in Microsoft Powerbi
Case Statement in Where Clause - SQL Server
SQL Server - Asynchronous Query Execution
Rails Brakeman Warning of SQL Injection