Hello everyone !
I'm practicing some advanced SQL queries and I'm facing a problem:
I'm trying to perform a COUNT that counts the return of lines (including null ones), but I always come across a syntax error.
Could you kindly help me? xD
SELECT
COUNT(*) AS QTD_LINE,
{Carros}.[Id],
{Carros}.[Placa],
{Carros}.[Marca],
{Proprietario}.[Nome],
{EntradaRegistro}.[DataTimeEntrada],
{EntradaRegistro}.[IdCarros],
{Carros}.[ProprietarioId],
NULL
FROM {Carros}
LEFT JOIN {Proprietario}
ON {Carros}.[ProprietarioId] = {Proprietario}.[Id]
LEFT JOIN {EntradaRegistro}
ON {Carros}.[Id] = {EntradaRegistro}.[IdCarros]
WHERE ({Carros}.[Id] <>
CASE
WHEN @IsCheckin = 1 AND {Carros}.[IsCheckin] <> 0 THEN 0
ELSE
WHEN @IsCheckin = 2 AND {Carros}.[IsCheckin] = 0 THEN 0
WHEN @IsCheckin = 0 THEN 0
END
END)
AND
(@SearchKey = '' OR {Carros}.[Placa] LIKE '%' + @SearchKey + '%')
UNION ALL
NULL,
{SaidaRegistro}.[Id]
FROM {SaidaRegistro}
INNER JOIN {EntradaRegistro}
ON {SaidaRegistro}.[Id] = {EntradaRegistro}.[IdCarros]
ORDER BY {Carros}.[Id]
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY
Hello @Gustavo Mor
It seems like you want to count the number of rows returned by your query while also selecting specific columns from different tables. The problem is that you're trying to select both the count and other columns without using a proper grouping mechanism.
Here's a modified version of your query that uses a subquery to calculate the count and then joins it with the rest of your data:
CountData.QTD_LINE,
Carros.Id,
Carros.Placa,
Carros.Marca,
Proprietario.Nome,
EntradaRegistro.DataTimeEntrada,
EntradaRegistro.IdCarros,
Carros.ProprietarioId,
NULL AS SomeColumn
FROM
Carros
LEFT JOIN Proprietario ON Carros.ProprietarioId = Proprietario.Id
LEFT JOIN EntradaRegistro ON Carros.Id = EntradaRegistro.IdCarros
LEFT JOIN (
SELECT COUNT(*) AS QTD_LINE
FROM Carros
WHERE
(Carros.Id <>
WHEN @IsCheckin = 1 AND Carros.IsCheckin <> 0 THEN 0
WHEN @IsCheckin = 2 AND Carros.IsCheckin = 0 THEN 0
AND (@SearchKey = '' OR Carros.Placa LIKE '%' + @SearchKey + '%')
) AS CountData ON 1=1
SaidaRegistro.Id
SaidaRegistro
INNER JOIN EntradaRegistro ON SaidaRegistro.Id = EntradaRegistro.IdCarros
ORDER BY Carros.Id
FETCH NEXT @PageSize ROWS ONLY;
In this version, I've added a subquery (CountData) that calculates the count, and then I've joined this subquery with the rest of your data. The ON 1=1 condition is used because you want to join on a constant value, and it ensures that every row from the main query is joined with the single row from the subquery.
Thanks & regards
Anees
Thank you, solve my problems! I'm still practicing SQL xD
Hi Gustavo
To use COUNT, you must use a GROUP BY to select for which rows the count must run. The same goes for the other aggregate functions MAX, MIN, SUM and AVG.
Example: if you want to count the number of customers in a city, the query will be:
SELECT city, count(*)
FROM customers
GROUP BY city
I think your query would look like this:
SELECT COUNT(*) AS QTD_LINE,
LEFT JOIN {Proprietario} ON {Carros}.[ProprietarioId] = {Proprietario}.[Id]
LEFT JOIN {EntradaRegistro} ON {Carros}.[Id] = {EntradaRegistro}.[IdCarros]
CASE WHEN @IsCheckin = 1 AND {Carros}.[IsCheckin] <> 0 THEN 0
ELSE CASE WHEN @IsCheckin = 2 AND {Carros}.[IsCheckin] = 0 THEN 0
ELSE CASE WHEN @IsCheckin = 0 THEN 0 END
AND (@SearchKey = '' OR {Carros}.[Placa] LIKE '%' + @SearchKey + '%')
GROUP BY {Carros}.[Id],
{Carros}.[ProprietarioId]
Best regards, Marlies