sample1
sample 2
more
UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.
There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates.
The following query returns always positive number. The condition is true when the summation is 2.
On line 3, the true is variable that setted (when is false = false doesnt continue to execute the subquery) from the code (when the user chosen the specific restriction)
GroupBy complex
table variable
JavaScript:
WITH table0(field1,field2,field3,field4) AS
(SELECT row_number() over (partition by table1.field4 order by table2.dateinserted) as field1 , table3.Description,
coalesce(table2.field11,table2.field12), table2.field4
from doctoraudits table2
inner join table1 on table1.field4 = table2.field4
inner join table4 on table4.field5 = table2.field5
left join tbl table3 on table3.id = table2.statusid
where table4.isactive=1 and table1.field4 in (select field4 from table5)
)
select count(*)
from table1 r
inner join table6 rd on rd.field4 = r.field4
inner join table7 d on d.field6 = rd.field6
inner join table6 rd2 on rd2.field6 = d.field6
left join table0 doc1 on doc1.field9 = r.field9 and doc1.field10 =1
left join table0 doc2 on doc2.field9 = r.field9 and doc2.field10 =2
left join table0 doc3 on doc3.field9 = r.field9 and doc3.field10 =3
where rd2.field7 is null and d.field8=1sample 2
JavaScript:
WITH Visits AS(
SELECT Albums.AlbumID,COUNT(a.Expr1) AS Visits
FROM
(SELECT SessID,SUBSTRING(username,8,(CHARINDEX(':',username,9)-8) ) AS Expr1 FROM SessionsLog where username like '%EVNUSR%') AS a
LEFT JOIN Albums ON a.Expr1 = Albums.EventAlbumCode
GROUP BY Albums.AlbumID
)
,
PhotoSelected AS(
SELECT Albums.AlbumID,COUNT(distinct PhotoSelections.PhotoID) AS PhotosSelected
FROM PhotoSelections
INNER JOIN Photos ON dbo.PhotoSelections.PhotoID = dbo.Photos.PhotoID
INNER JOIN Albums ON dbo.PhotoSelections.AlbumID = dbo.Albums.AlbumID AND dbo.Photos.AlbumID = dbo.Albums.AlbumID
INNER JOIN Carts ON dbo.PhotoSelections.CartID = dbo.Carts.CartID
INNER JOIN OrderCarts ON dbo.Carts.CartID = dbo.OrderCarts.CartID
where (PhotoSelections.Quantity IS NOT NULL) AND (dbo.PhotoSelections.Quantity > 0) and Carts.CartID is not null
GROUP BY Albums.AlbumID, Albums.TotalPictures
),
CustomOrder as ( select Albums.AlbumID,Carts.TotalCopies,Orders.OrderID from albums
INNER JOIN Orders ON Albums.AlbumID = Orders.AlbumID
INNER JOIN OrderCarts ON dbo.Orders.OrderID = OrderCarts.OrderID
INNER JOIN Carts ON OrderCarts.CartID = Carts.CartID
)
SELECT ISNULL(SUM(distinct CustomOrder.TotalCopies),0) as TotalCopies,
categoryname,albums.AlbumName, albums.TotalPictures,(Visits) as PageVisits,
count(CustomOrder.AlbumID) as [Bills], ISNULL(SUM(CustomOrder.TotalCopies),0) as [PrintTotal],
ISNULL((PhotoSelected.PhotosSelected),0) AS SelectedPictures
FROM Albums
LEFT JOIN Visits ON Albums.AlbumID = Visits.AlbumID
LEFT JOIN CustomOrder on Albums.AlbumID =CustomOrder.AlbumID
LEFT JOIN AlbumCategories on AlbumCategories.AlbumCategoryID = albums.AlbumCategoryID
LEFT JOIN PhotoSelected ON Albums.AlbumID = PhotoSelected.AlbumID
where albums.isevent = 1
AND (Albums.AlbumCategoryID = @AlbumCategoryID OR @AlbumCategoryID IS NULL)
AND (albums.ClientID = @ClientID OR @ClientID IS NULL)
AND albums.IsDeleted = 0
AND (albums.EventDate >= @EventdateFrom OR @EventdateFrom IS NULL)
AND (albums.EventDate <= @EventdateTo OR @EventdateTo IS NULL)
--albums.albumid=769
group by categoryname,albums.AlbumName, Visits.Visits, albums.TotalPictures,PhotoSelected.PhotosSelected
order by categoryname DESC,albums.AlbumName DESCmore
UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.
There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates.
JavaScript:
--https://stackoverflow.com/a/49928
--UNION Example:
SELECT 'foo' AS bar UNION SELECT 'foo' AS bar
--UNION ALL example:
SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar
JavaScript:
--https://forums.techguy.org/threads/solved-sql-how-to-use-group-by-with-union.539870/#post-4406173
SELECT AVG(date_completed-login_date),
to_char(to_date(login_date), 'YYYY') AS wYear
FROM
(SELECT test.date_completed 'date_completed',
sample.login_date 'login_date')
FROM sample
JOIN test ON sample.id_numeric = test.sample
UNION ALL
SELECT c_test.date_completed 'date_completed',
c_sample.login_date 'login_date'
FROM c_sample
JOIN c_test ON c_sample.id_numeric = c_test.sample ) AS tbl
GROUP BY to_char(to_date(login_date), 'YYYY')The following query returns always positive number. The condition is true when the summation is 2.
On line 3, the true is variable that setted (when is false = false doesnt continue to execute the subquery) from the code (when the user chosen the specific restriction)
JavaScript:
select * from tableA
where tableA.fieldA = 1 and
( lower('true')='false' or (
select abs(sum(t.soula))makis
from
(
SELECT CASE
WHEN tableB.statusid =
(SELECT id
FROM tableBstatuses
WHERE NAME = 'Approved')
THEN 1
WHEN tableB.statusid =
(SELECT id
FROM tableBstatuses
WHERE NAME = 'Denied')
THEN -1
ELSE 0
END AS soula
FROM tableB
WHERE tableB.recID = tableA.recID
AND tableA.fieldB = 'Pending'
) t
)=2
)GroupBy complex
JavaScript:
select playerid
from players where badge in (
select badge from players group by badge having count(*)>1
) and playerid not in (
select playerid from requests
)table variable
JavaScript:
--DATEDIFF - https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017
--Table variable - https://searchsqlserver.techtarget.com/tutorial/Table-variables
--test on SQL2008 - 670k records - 29min (6fields)
USE master
GO
DECLARE @start_dt DATETIME;
DECLARE @end_dt DATETIME;
set @start_dt = GETDATE();
print 'STARTED @ ' + convert(nvarchar(25), @start_dt) + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);
DECLARE @table_counter bigint;
set @table_counter = (select count(*) from Northwind..categories);
print 'source table total records : ' + convert(nvarchar(25),@table_counter);
set @table_counter = (select count(*) from DestDB..Categories);
print 'destination table total records : ' + convert(nvarchar(25),@table_counter);
--TABLE variable
DECLARE @tmp_ids TABLE (id INT)
--INSERT the temporary record for the while
insert into @tmp_ids select top 1000 CategoryID from Northwind..Categories
WHILE (select count(*) from @tmp_ids) > 0
BEGIN
--INSERT to DESTINATION TABLE
insert into DestDB..Categories (CategoryName,Description,Picture)
(select CategoryName,Description,Picture from Northwind..categories where categoryid in (select id from @tmp_ids))
--delete from SOURCE TABLE incl. temporary
delete from Northwind..categories where categoryid in (select id from @tmp_ids)
delete from @tmp_ids
--INSERT to SOURCE TABLE to VARIABLE
Insert into @tmp_ids select top 1000 CategoryID from Northwind..Categories
END
set @end_dt = GETDATE();
print CHAR(13)+CHAR(10) + 'COMPLETED @ ' + convert(nvarchar(25), @end_dt) + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);
print 'Duration in minutes : ' + convert(nvarchar(8),DATEDIFF(minute,@start_dt, @end_dt)) + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);
print 'Duration in seconds : ' + convert(nvarchar(8),DATEDIFF(second,@start_dt, @end_dt)) + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);
print 'Duration in milliseconds : ' + convert(nvarchar(8),DATEDIFF(millisecond,@start_dt, @end_dt)) + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);
set @table_counter = (select count(*) from Northwind..categories);
print 'source table total records : ' + convert(nvarchar(25),@table_counter);
set @table_counter = (select count(*) from DestDB..Categories);
print 'destination table total records : ' + convert(nvarchar(25),@table_counter);