logo
down
shadow

Oracle SQL group data by value


Oracle SQL group data by value

By : user2949713
Date : November 17 2020, 04:28 AM
this one helps. Assuming I'm understanding your question correctly, you're looking for conditional aggregation, something you can do with max and case:
code :
select id, 
    max(case when attr_name = 'XPTA' then attr_id end) xpta,
    max(case when attr_name = 'XPTO' then attr_id end) xpto,
    start_date
from table_a 
group by id, start_date


Share : facebook icon twitter icon
Oracle group data

Oracle group data


By : user3579898
Date : March 29 2020, 07:55 AM
will help you I don't use ROLLUP often, but the difficulty seems to be that once the ROLLUP is applied, there is no connection between the summary rows and the detail rows, so you can't easily sort them as groups within the overall result set, which is what I think you want to do.
I think this will get what you want, but it could be inefficient on large data sets; it essentially calculates the subtotal values twice, once with rollup, and once with an analytic function.
code :
select * 
  from (select m.col1, 
               m.col2, 
               m.col3, 
               sum(m.col4) sum_c4
          from mytable m
        group by rollup(m.col1, (m.col2, m.col3))
       )
order by case when col1 is null then 1 
              else 0 
         end asc,                                     -- put grand total line at end of entire set
         sum(sum_c4) over (partition by col1) desc,   -- sort subgroups by descending subtotal
         col1,                                        -- tiebreaker for subgroups with same total
         case when col2 is null then 1 
              else 0 
         end asc,                                     -- put subtotal line at end of each group
         sum_c4 desc                                  -- put detail lines in descending order
unable to group the data in ORACLE with GROUP BY clause

unable to group the data in ORACLE with GROUP BY clause


By : Igor Koch
Date : March 29 2020, 07:55 AM
I hope this helps you . While using the USING clause in a query, specifying table name is redundant. Only the column name is enough information for the database. For example - select office_id. Try this :
code :
select office_id,
count(staff_id)
from offices join  staffs using (office_id) join managers using (manager_id)
group by office_id;
Group a data in Unix shell or Group by in Oracle

Group a data in Unix shell or Group by in Oracle


By : Anna Hershenfeld
Date : March 29 2020, 07:55 AM
With these it helps You don't give a whole lot of information to go on.
In general, doing operations in the database will be faster than moving the data to another environment. There are several reasons for this:
Oracle : Error ORA-00937: not a single-group group function in Oracle Join Query

Oracle : Error ORA-00937: not a single-group group function in Oracle Join Query


By : user3754118
Date : March 29 2020, 07:55 AM
I wish this help you I suppose you have to add cy.continent to the select statement of your first try when you group by Cy.continent.
Try something like that (floor is for the "round it down to the nearest integer" - part):
code :
SELECT floor(AVG(City.population)),Country.continent FROM City JOIN Country ON City.CountryCode = Country.Code
GROUP BY Country.continent;
Group data in Oracle

Group data in Oracle


By : user1871084
Date : March 29 2020, 07:55 AM
it helps some times The requirement is really hard to understand. From how I see it:
Pick one record of tbl_pattern. Take all records of tbl_main. That's a CROSS JOIN. If a tbl_main record matches the tbl_pattern record and the tbl_pattern record matches a row in tbl_parent then status is 2. If a tbl_main record plus the tbl_pattern record's request_no matches a row in tbl_child then this row's status is to be shown.
code :
select 
  m.*, 
  p.supplier_id,
  case 
    when p.order_no = m.order_no 
    and p.code = m.code
    and p.disp_order = m.disp_order
    and p.lvl = m.lvl
    and p.description = m.description
    and (p.order_no, p.code, p.disp_order, p.lvl, p.description, p.request_no) in
    (
      select order_no, code, disp_order, lvl, description, request_no
      from tbl_parent
    ) then 2
    else
    (
      select state
      from tbl_child c
      where c.order_no = m.order_no
        and c.code = m.code
        and c.disp_order = m.disp_order
        and c.lvl = m.lvl
        and c.description = m.description
        and c.request_no = p.request_no
    )
  end as status
from tbl_pattern p
cross join tbl_main m
where p.order_no = 'RM001-01'
  and p.code = 1
  and p.disp_order = 0
  and ...;
Related Posts Related Posts :
  • SQL Server error: Column name or number of supplied values does not match table definition
  • How to find a table name which's column name consist cl_
  • Cannot access existing table from WCF Code
  • SQL CREATE VIEW Error
  • Many Statement in the same transaction PLSQL
  • Tables are not joining properly in sql server 2008
  • DB2 how to sum two column from two different table
  • SQL msAccess: list unfinished courses of a student
  • SQL Server delete multiple rows
  • Finding max(date) from the resultant query output
  • updating multiple columns using case statement in sql server
  • Nested SQL query having select subquery in update statement
  • "putting" a certain object to the screen from inside an array
  • Retrieve segment from value
  • Error while creating a table in oracle 11g
  • Transaction with Sequelize doesn't work
  • Search time complexity of this sql query
  • How to return unique records between two tables without using distinct and union?
  • Is it possible to maximum occurences of a value for each key in my table all in one query?
  • Divide table into groups of 5000 based on rank
  • rolling back exec and sp_OA methods
  • How to convert multiple rows to one row in SQL Server?
  • How to use substring and charindex in google query language
  • Find table information where part of column matches char variable
  • Need to retrieve rows from table where the following condition was not satisfied column A = column B and column B = colu
  • Assigning an index to a constraint
  • Retrieve records from multiple Records returned by Sub-Query
  • SQL DELETE group of records based on opposite group being empty
  • SQL GetDate() returns wrong time
  • Total sales amount for customers in cities
  • SQL Server -- updating the `sys.*` tables and not just reading them
  • SQL Order By using concat
  • SQL server 2008 - subquery returned more than one value
  • Group Data Hourly and Insert Into a Summary Table in Postgres
  • Execute SQL Server Pass-Through Query From Access VBA
  • MS Access SQL Transform Aggregate Manipluation of Values for Pivot
  • SQL "if exists..." dynamic query
  • HANA: Split string?
  • Create a list of dates and match records to the list
  • Why won't SQL round correctly from my batch script?
  • SQLServer - Multiple PIVOT on same columns
  • Update a table foreign keys by substituting by a lookup from the result of another table
  • Deleting rows in a single database table based on the values of other rows
  • Search and replace part of string in database - what are the pitfalls?
  • How could I generate auto insert function SEAT_NO like A1,A2.... B1,B2 in sql server
  • whats a good way to optimize a table? SQL server 2k8
  • Sql server dynamic sequences
  • JPQL Query - object that's in one list and isn't in another
  • How to sort lines in select resquest to don't have multiple same lines
  • SQL Server : new and old value from history table in one row
  • SQL select bottom half
  • How to sum order cost
  • Repeatedly creating and dropping temp table of the same name
  • oracle yyyyiw glitch
  • ORDER BY id IN Subquery
  • SQL max concurrent sessions per hour of day
  • postgresql events per hours
  • database db2: Group by custom date range
  • How to allow letters only in VARCHAR2 column (Oracle SQL)
  • What is the equivalent of T-SQL ISNUMERIC function in HANA Sqlscript?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org