How to Combine These Two SQL Statements

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 nulls:

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



Leave a reply



Submit