logo
Tags down

shadow

Combine WITH statement with UNION


By : Kamel Abbassi
Date : October 14 2020, 08:10 PM
I wish this help you I have the following query, and i want the output in a single row with the delimeter | separating my data :
code :
    select 'WLT_CUSTOMER'||'|'||(
    select COUNT(*) as TOTAL_WLT_CUSTOMERS 
    FROM WLT_CUSTOMER 
    where WLT_DATE = (SELECT SCHEDULED_DATE FROM BANK_PARAMETERS))
    ||'|'||'CUSTOMER'||'|'||
    (select  COUNT(*) as TOTAL_CUSTOMERS 
    FROM CUSTOMER 
    where CUST_ID <> 0) from dual;


Share : facebook icon twitter icon

How do I combine two queries (union all) into one row?


By : brt235
Date : March 29 2020, 07:55 AM
Hope that helps I don't understand the problem with sub-querying, it seems like it should be just as fast:
code :
select
    sub.gn as groupname,
    sum(sub.nj) as NormJobs, sum(sun.nc) as NormCpu,
    sum(sub.sj) as Sys7Jobs, sum(sub.sc) as Sys7Cpu
  from (
      select
          groupname as gn,
          sum(jobs) as nj, sum(cpu) as nc,
          0 as sj, 0 as sc
        from tbl
          where subsys = 'NORM'
          group by groupname
        union all select
            groupname as gn,
            0 as nj, 0 as nc,
            sum(jobs) as sj, sum(cpu) as sc
          from tbl
          where subsys = 'SYS7'
          group by groupname
    ) as sub
    group by sub.gn
    order by 1

Can you define values in a SQL statement that you can join/union, but are not stored in a table outside of the statement


By : KEITH PHILLIPS
Date : March 29 2020, 07:55 AM
This might help you Sure, you can use a UNION ALL inside a subselect and join with the result of that. Something like this might do the trick:
code :
SELECT *
FROM table1 T1
JOIN (
    SELECT titles, stuff 
    FROM table2
    UNION ALL
    SELECT 'foo' AS titles, 'foostuff' AS stuff
    UNION ALL
    SELECT 'bar' AS titles, 'barstuff' AS stuff
) T2
ON T1.id = T2.titles

How can I select mysql data with a union statement where the data matches each select statement, i.e. combine the AND op


By : mekuate
Date : March 29 2020, 07:55 AM
wish help you to fix your issue Lets say I have a table with just two columns: name and mood. A row holds a persons name, and their mood, if they have multiple moods, then multiple rows are stored in the DB. , Provided you have no duplicates, you can do it with a subquery:
code :
SELECT `name` FROM (
    SELECT `name`, COUNT(*) AS `count` FROM `moods`
    WHERE `mood` IN ('Excited', 'Happy', 'Proud') GROUP BY `name`
) WHERE `count` = 3
SELECT `m1`.`name` FROM `moods` `m1`
JOIN `moods` `m2` USING (`name`)
JOIN `moods` `m3` USING (`name`)
WHERE `m1`.`mood` = 'Excited' AND `m2`.`mood` = 'Happy' AND `m3`.`mood` = 'Proud'
SELECT `name` FROM `moods`
WHERE `mood` IN ('Excited', 'Happy', 'Proud')
GROUP BY `name`
HAVING COUNT(*)>3

Apart from Union , Union all and Pivot in MS- Sql server can I combine two columns into one column


By : Mohini Rana
Date : March 29 2020, 07:55 AM
I wish this helpful for you I am having a result set like
code :
SELECT T.x
FROM table
     CROSS APPLY (
         VALUES (Col1), (Col2)
     ) AS T(x)

Combine Union ALL with Inner Join Statement


By : Abdul Basit
Date : March 29 2020, 07:55 AM
this one helps. I have a problem how to combine the sql statements (inner join and union all. as a Newbie in SQL. Hopefully all members can help me to solve the problems.attached herewith the SQL. The leave_Trx and leave_History contain same values that need to be union. Thank you.
code :
 select  t.pers_ID,t.startdt, t.enddt,t.noday,t.createdDT,
      (case when t.approveST = 'Y' then 'Approved' when t.approveST = 'N' then 'Not Approved' else 'Pending' end) as appST
      from leave_Trx t
      inner join leave_MType m on m.typeID = t.pers_ID
      inner join hr_personaldata l on l.pers_ID = @pers_ID
      where year(t.startdt) = @yyear and t.status = 'A'
      union all
      select h.pers_ID, h.startdt, h.enddt,h.noday,h.createdDT,
      (case when h.approveST = 'Y' then 'Approved' when h.approveST = 'N' then 'Not Approved' else 'Pending' end) as appST 
      from leave_History h
      inner join leave_MType m on m.typeID = h.pers_ID
      inner join hr_personaldata b on b.pers_ID = @pers_ID
      where year(h.startdt) = @yyear and h.status = 'A'
Related Posts Related Posts :
  • Unable to query multiple tables via XML: Error occurred in XML processing
  • SQL first date for the first occurrence of a value
  • Update multiple columns Table from a select statement
  • Is it possible to find (in an ordered table) multiple rows in sequence?
  • Query to return month-wise count in PostgreSQL
  • adding years getting invalid number error in case statement only - ORA-01722
  • Insert into a table using multiple tables
  • How to create a SQL Server Pivot Table
  • My SQL - COUNT field on multitable group by other field
  • How to order by a different column and then fetch offset rows in SQL Server?
  • Query builder Eloquent using Eager Loading in Laravel
  • What is the complexity of the CUBE operator in SQL?
  • IIF statement MS ACCESS VBA SQL
  • SQL Server group by?
  • LIKE in Elasticsearch for large texts
  • SQL - dynamic sum based on dynamic date range
  • SQL Order By: Specific Value First, then Ordering?
  • SQL Developer - SELECT statement with AVG calculation displaying multiple averages?
  • Microsoft SQL Server : return only the rows with the most recent date for each unique ID
  • How to create a queue like structure in SQL Server
  • Cross apply on columns on SQL server. Syntax error near )
  • Select count of subquery results, along with another field
  • SQL Server 2016 - Five Column to single row
  • Insert zero values for unexisting groups in Redshift
  • SQL LIKE '%...' in vba HTTP request
  • SQL Developer - How do I get MAX & AVG to draw from one column and filter from themselves?
  • How to optimize SQL query with multiple selects
  • Postgresql 11 - Create Procedure to Execute COPY function
  • Oracle VIEW - Convert VARCHAR2 to DATE and find MIN/MAX of DATE
  • In SQL, Main table should match with lookup table for the multiple columns
  • Very slow performance for inner join with restrictive where clause (very small subset of rows)
  • oracle sql 2 columns in one column
  • SQL query to get conflicting values in JSONB from a group
  • Two queries with same tables and join logic but different results
  • How to update a table joined with another table multiple times in Oracle?
  • Crystal Report how can I get data for every 5th of months
  • Finding MAX() from a serial number field which contains characters in SQL Server
  • Getting all distinct values and sorting them by hour
  • Database Compatibility Level
  • Self Nested SQL join
  • My store procedure only executes my first select statement while I wrote three?
  • Inserting Date gives error ORA-01861: literal does not match format string
  • Need to join row data into columns in Oracle
  • Syntax error Missing operator in my case statement
  • Filter the values from the source in merge query in mssql
  • How can i replace/convert number to string
  • Updating a table, on a button press, with selected information
  • Oracle SQL report generation without null
  • SQL get the names of the columns that are stored in a different table
  • Number masking & nls_parameter
  • Delete duplicate roles for SQL server
  • SQL Server: How to change collation of all columns of my select on the fly
  • SQL insert into statement leads to weird (cartesian product) result
  • check constraint sql server
  • Group specific number of rows together and generate a group id
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org