Combine these two SQL statements into one
You can do it with a left join of t2
to t1
:
delete t
from (
select * from t2
where name = (select source from info)
) t left join t1
on t1.name = t.name and t1.invoice = t.invoice and t1.total = t.total
where t1.name is null
See the demo.
If you want to use NOT EXISTS
:
delete t
from (
select * from t2
where name = (select source from info)
) t
where not exists (
select 1 from t1
where name = t.name and invoice = t.invoice and total = t.total
)
See the demo.
Results (rows remaining in t2
):
> name | invoice | total
> :--- | ------: | ----:
> C1 | 1 | 150
> C1 | 2 | 300
> C2 | 1 | 200
> C2 | 2 | 165
Is it possible to combine these two sql statements into one statement using group by?
You could count a couple of case
expressions:
SELECT client_type,
COUNT(CASE gender WHEN 'Male' THEN 1 END) AS num_males,
COUNT(CASE gender WHEN 'Female' THEN 1 END) AS num_females
FROM clients
GROUP BY client_type;
How do I combine 2 select statements into one?
You have two choices here. The first is to have two result sets which will set 'Test1' or 'Test2' based on the condition in the WHERE
clause, and then UNION
them together:
select
'Test1', *
from
TABLE
Where
CCC='D' AND DDD='X' AND exists(select ...)
UNION
select
'Test2', *
from
TABLE
Where
CCC<>'D' AND DDD='X' AND exists(select ...)
This might be an issue, because you are going to effectively scan/seek on TABLE twice.
The other solution would be to select from the table once, and set 'Test1' or 'Test2' based on the conditions in TABLE:
select
case
when CCC='D' AND DDD='X' AND exists(select ...) then 'Test1'
when CCC<>'D' AND DDD='X' AND exists(select ...) then 'Test2'
end,
*
from
TABLE
Where
(CCC='D' AND DDD='X' AND exists(select ...)) or
(CCC<>'D' AND DDD='X' AND exists(select ...))
The catch here being that you will have to duplicate the filter conditions in the CASE
statement and the WHERE
statement.
Combining the results of two SQL queries as separate columns
You can aliasing both query and Selecting them in the select query
http://sqlfiddle.com/#!2/ca27b/1
SELECT x.a, y.b FROM (SELECT * from a) as x, (SELECT * FROM b) as y
How to combine these two sql statements
I would suggest that you do the calculation of the min and max values in a single subquery. Then you can simply take the rows that you want using a where
clause to filter:
SELECT s.`pres_id`, COUNT( `pres_id` ), ROUND( AVG( `tot_score` ) , 2 ) ,
`username`, `name`, `pres_day`
FROM `scores` s join
`users` u
on u.id_user = s.pres_id join
(select s.pres_id, max(tot_score) as maxts, min(tot_score) as mints
from scores s
group by s.pres_id
) ssum
on u.id_user = ssum.pres_id
where s.tot_score > ssum.mints and s.tot_score < ssum.maxts
GROUP BY s.`pres_id`;
I am guessing that you want to strip the highest and lowest scores for each student. If you just want to omit the highest and lowest for all students, then use the following:
SELECT s.`pres_id`, COUNT( `pres_id` ), ROUND( AVG( `tot_score` ) , 2 ) ,
`username`, `name`, `pres_day`
FROM `scores` s join
`users` u
on u.id_user = s.pres_id cross join
(select max(tot_score) as maxts, min(tot_score) as mints
from scores s
) ssum
where s.tot_score > ssum.mints and s.tot_score < ssum.maxts
GROUP BY s.`pres_id`;
EDIT:
Modification to keep score when there is only one score for a student:
SELECT s.`pres_id`, COUNT( `pres_id` ), ROUND( AVG( `tot_score` ) , 2 ) ,
`username`, `name`, `pres_day`
FROM `scores` s join
`users` u
on u.id_user = s.pres_id join
(select s.pres_id, max(tot_score) as maxts, min(tot_score) as mints,
count(*) as cnt
from scores s
group by s.pres_id
) ssum
on u.id_user = ssum.pres_id
where (s.tot_score > ssum.mints and s.tot_score < ssum.maxts) or (cnt = 1)
GROUP BY s.`pres_id`;
How to combine two sql queries into one
Use a UNION query - just stuff "UNION" between the two queries:
SELECT SUM(...) AS AEROWiz
FROM ...
UNION
SELECT SUM(...) AS AEROWiz
FROM ...
update
wrap the union in yet another query:
SELECT SUM(AEROWiz)
FROM (
.... unioned queries here
) AS child
How to combine these two SQL statements?
Something like this?
select sum(case when ws.ID_WorkflowType = 1 then 1 else 0 end) as cntCM_PRWK
, sum(case when ws.ID_WorkflowType = 3 then 1 else 0 end) as cntCM_CMQ
from dbo.CaseWorkflow cw
join vew_CasePersonnelSystemIDs vcps on cw.ID_Case = vcps.ID_Case
join dbo.WorkflowStates ws on ws.ID_WorkflowState = cw.ID_WorkflowState
where CMSUID = @nSUID
Combining two SQL statements that use an aggregate sum function
You can use a derived table:
select ...
from capital_project
left join (
< your query from the view goes here >
) as capital_cost_sum ON ...
Or use a common table expression
with capital_cost_sum as (
< your query from the view goes here >
)
select ...
from capital_project
left join capital_cost_sum ON ...
Combining two SQL statements with same stem but different where clause
You can move the case
expression inside the second count
call and use the fact that count
(and many similar aggregate functions) skips null
s:
SELECT COUNT(*) AS total_number_of_followups_scheduled,
COUNT(CASE status WHEN 'Completed' THEN 1 END) AS number_followups_completed
FROM promis_lt
Related Topics
Join Versus Exists Performance
Postgres 9.4 JSONb Array as Table
SQL Trigger After Insert Update Another Table with Conditions
Is There a Function That Takes a Year, Month and Day to Create a Date in Postgresql
Rails Activerecord Where or Clause
How to Convert SQL Server to Oracle
How to Make String Auto Increment
Print Start and End Date in One Row for Continous or Overlaping Date Ranges in Oracle SQL
Ssis Pass Datasource Between Control Flow Tasks
Exclude Rows Based on Other Rows (Sql)
Mysql: Union of a Left Join with a Right Join
SQL Server Concatenate Group By
Sql, on Delete Cascade and on Update Cascade
SQL Where in (...) Sort by Order of the List
Mysql: Searching Between Dates Stored as Varchar
How to Return a New Identity Column Value from an SQLserver Select Statement