Sql: Two Select Statements in One Query

SQL: Two select statements in one query

You can do something like this:

 (SELECT
    name, games, goals
    FROM tblMadrid WHERE name = 'ronaldo')
 UNION
 (SELECT
    name, games, goals
    FROM tblBarcelona WHERE name = 'messi')
ORDER BY goals;

See, for example: https://dev.mysql.com/doc/refman/5.0/en/union.html

Using two Select statements in one query to bring back desired data set

could be using an inner join on subquery group by login

    SELECT W1.login, W1.TRADE_AMT, YTD.TRADE_AMT
    FROM 
    (SELECT login, sum(TRADE_AMT) AS TRADE_AMT FROM CORPS
        WHERE DATE > 20180903 AND DATE_ID <= 20180910 
        AND REGION = 'London'
        AND Rating = 'High'
        AND LOGIN IN ('ITI1','RAB0','RR12') 
      GROUP BY LOGIN) AS W1
    LEFT JOIN (
    SELECT login, sum(TRADE_AMT) AS TRADE_AMT FROM CORPS
    WHERE DATE > 20180101 AND DATE_ID < 20180911 
        AND REGION = 'London'
        AND Rating = 'High'
        AND LOGIN IN ('ITI1','RAB0','RR12') 
        GROUP BY LOGIN ) AS YTD ON YTD.LOGIN =  W1.LOGIN 

left join show also not matching login values but you can use inner join if you need oly matching login between week and year

Run two select statements in one view?

The temp table select could be converted to be a CTE (With clause), and the 2nd part the select query of the view.

Alternatively you could just inline it with sub-selects, but depending on complexity that might make it harder to maintain.

CREATE VIEW yourView AS
WITH myFirstSelect(someFields) AS
(
    SELECT somefields FROM sometable
)
SELECT * from myFirstSelect

Docs : https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017

How to combine two select statements into one select statement in MSSQL?

Normally, you would use an inner join for things like that. But inner join needs some common columns between the two objects it joins, and you don't have that.

Since your queries also does not contain an ORDER BY clause, there is no reliable way to join them so that each row in table1 will always be joined to the same row in table2.

However, since both tables have a time column, you can use that:

;WITH CTE1 AS
(
    SELECT  a1, 
            b1, 
            c1,
            ROW_NUMBER() OVER(ORDER BY [time]) AS rn
    FROM Table1
    WHERE time between '2018-03-05' and '2018-03-06'
), CTE2 AS
(
    SELECT  a2, 
            b2, 
            c2,
            ROW_NUMBER() OVER(ORDER BY [time]) AS rn
    FROM Table2
    WHERE time between '2018-03-05' and '2018-03-06'
)

SELECT t1.a1, t1.b1, t1.c1, t2.a2, t2.b2, t2.c2                     
FROM cte1 as t1
INNER JOIN cte2 as t2 ON t1.rn = t2.rn

Divide the results of two select queries

You may use conditional aggregation and simple division in one query:

select
  100.0 *
  count(
    case when ISOGEN_LINE_PROGRESS_ = 'C'
    then 1
    end
  )
  /
  nullif(count(
    case when PROJECT_NUMBER_ = 'PJ001234'
    then 1
    end
  ), 0) as rate_
FROM dbo.PID_Components_PROCESS_LINES
WHERE ISOGEN_LINE_PROGRESS_ = 'C'
  or PROJECT_NUMBER_ = 'PJ001234'

SQL: Two select statements in one query ( Same Table )

Use conditional aggregation:

SELECT
    AVG(CASE WHEN school = 'school A' THEN score END) AS school_a_average,
    AVG(CASE WHEN school != 'school A' THEN score END) AS other_school_average,
    SUM(school = 'school A') - SUM(school != 'school A') AS diff
FROM infoTable;

SQL: Performing a calculation on columns from two select statements in one query

I think you just need conditional aggregation:

SELECT g.shopname, COUNT(s.sold),
       SUM(CASE WHEN s.datereturned > '2010-01-01' AND s.returned IS NOT NULL THEN 1 ELSE 0 END) as returned,
       (COUNT(s.sold) - 
        SUM(CASE WHEN s.datereturned > '2010-01-01' AND s.returned IS NOT NULL THEN 1 ELSE 0 END)
       ) as net
FROM global INNER JOIN
     stock
     ON g.id = s.shop
GROUP BY g.shopname;

Having the date logic only apply to returns is strange. Also, I am guessing, though, that you want SUM() of the inventory and not COUNT(). So this may produce more accurate results:

SELECT g.shopname, SUM(s.sold) as items_sold,
       SUM(CASE WHEN s.datereturned > '2010-01-01' THEN s.returned ELSE 0 END) as items_returned,
       SUM(CASE WHEN s.datereturned > '2010-01-01' THEN s.items_sold - s.returned
                ELSE s.items_sold END)
FROM global INNER JOIN
     stock
     ON g.id = s.shop
GROUP BY g.shopname;

How to combine Two Select statement to One SQL Statement

You can use join between 2query by using sub-query

select max(t1.TIMEIN),min(t2.TIMEOUT),t1.DATE,t1.EMP_Token_No from
    (
    SELECT
    EMP_Punch_Date as 'DATE',
    MAX(EMP_Punch_Time) as 'TIMEIN'
    FROM EMP_Event_Log
    WHERE EMP_Punch_Date='09-02-2018'
    GROUP BY EMP_Token_No,EMP_Name,EMP_Punch_Date,EMP_Punch_Time
    ) as t1
    join
    (
    SELECT 
    EMP_Punch_Date as 'DATE',
    MIN(EMP_Punch_Time) as 'TIMEOUT'
    FROM EMP_Event_Log
    WHERE EMP_Punch_Date='09-03-2018'
    GROUP BY EMP_Token_No,EMP_Name,EMP_Punch_Date,EMP_Punch_Time
    ) t2 on t1.EMP_Token_No=t2.EMP_Token_No 
    group by t1.DATE,t1.EMP_Token_No

sql remove common from two select statements

I am not sure what columns names define, but just to give you some idea. Find Employees who are not in the not working list.

You can also use Left join and see which one is faster.

-- dummy query based on your inputs

SELECT      Medarbejdere.navn AS Medarbejder
FROM        Medarbejdere
where Medarbejdere.id not in (
SELECT      Medarbejdere.id
FROM        Medarbejdere, Fravaer
WHERE       Medarbejdere.id = Fravaer.medarbejder
AND         Fravaer.slut IS NULL )

-- second option, based on sample data
SELECT *
FROM    Medarbejdere m
LEFT JOIN Fravaer f
    ON f.medarbejder = m.ID
WHERE f.slut IS NOT NULL and f.slut>= '2018-09-01'; //you can use generic 
currentdate option

---

Related Topics