CODEWITHSUNDEEP
sqlsql-server
Mattias
Mattias

Reputation: 3179

How to get result of two querys in the same result within SQL Server?

I have two queries that give me the result. What I am trying to achieve is to get the result of the first query on the top (as the first value), and then the results of the second query under that, so the exact result will come first (Inlagg.Categori = 'testcategory'), and then if there is more results with the LIKE 'testcategory%' show them under and order them after amount of categories.

How do I match these queries together? 

SELECT Inlagg.Categori, COUNT(Inlagg.Categori) AS 'amount' FROM Inlagg 
WHERE Inlagg.Date > '2015-04-26' AND Inlagg.Categori = 'testcatego'
GROUP BY Inlagg.Categori, Inlagg.Date

SELECT Inlagg.Categori, COUNT(Inlagg.Categori) AS 'amount' FROM Inlagg 
WHERE Inlagg.DatE > '2015-04-26' AND Inlagg.Kategori LIKE 'testcatego%'
GROUP BY Inlagg.Categori, Inlagg.Date
ORDER BY amount DESC

Upvotes: 0

Views: 61

Answers (3)

shA.t
shA.t

Reputation: 16968

As Parado's answer is in right way nad you need to ORDER BY amount DESC I suggest you this query

SELECT *
FROM (
    SELECT Inlagg.Categori, COUNT(Inlagg.Categori) AS 'amount', '0' As Ord
    FROM Inlagg 
    WHERE Inlagg.Date > '2015-04-26' AND Inlagg.Categori = 'testcatego'
    GROUP BY Inlagg.Categori, Inlagg.Date
  UNION ALL
    SELECT Inlagg.Categori, COUNT(Inlagg.Categori) AS 'amount', '1' As ord
    FROM Inlagg 
    WHERE Inlagg.Datum > '2015-04-26' AND Inlagg.Kategori LIKE 'testcatego%'
    GROUP BY Inlagg.Categori, Inlagg.Date ) DT
ORDER BY ord, amount DESC

Trick: ord will keep the number of the query.

Upvotes: 2

A ツ
A ツ

Reputation: 1267

just write one simple query and sort the testcat on top :

SELECT Inlagg.Categori, COUNT(Inlagg.Categori) AS 'amount', '1' As ord
FROM Inlagg 
WHERE Inlagg.Datum > '2015-04-26' AND Inlagg.Categori LIKE 'testcatego%'
GROUP BY Inlagg.Categori, Inlagg.Date
ORDER BY CASE WHEN Inlagg.Categori = 'testcatego' THEN 1 ELSE 2 END,
         Inlagg.Date

the case checks if the categori is equal and translates it to 1. otherwise its 2.

Upvotes: 0

Robert
Robert

Reputation: 25763

Try to use UNION ALL as below

SELECT Inlagg.Categori, COUNT(Inlagg.Categori) AS 'amount' FROM Inlagg 
WHERE Inlagg.Date > '2015-04-26' AND Inlagg.Categori = 'testcatego'
GROUP BY Inlagg.Categori, Inlagg.Date
UNION ALL
SELECT Inlagg.Categori, COUNT(Inlagg.Categori) AS 'amount' FROM Inlagg 
WHERE Inlagg.Datum > '2015-04-26' AND Inlagg.Kategori LIKE 'testcatego%'
GROUP BY Inlagg.Categori, Inlagg.Date

Upvotes: 1

Related Questions