CODEWITHSUNDEEP
mysql
Nisha Jain
Nisha Jain

Reputation: 25

Get Seperate Total of Two MySQL table

I have two table called questions and entries. I want get separate total of both table using one query. Like questions 10 and entries 20. I have tried like below but its giving me two row as totalEntries 

SELECT COUNT(*) as totalEntries FROM quiz_entries UNION SELECT COUNT(*) as totalQuestions FROM quiz_questions

Let me know if any one can help me for get seperate result for both table like totalEntries 20 and totalQuestions 10.

Thanks!

Upvotes: 1

Views: 33

Answers (2)

Vikas Chaturvedi
Vikas Chaturvedi

Reputation: 517

You can do by this also. Because temp table solves our complex problem too.

IF(OBJECT_ID('tempdb..#tblEntries')) IS NOT NULL
DROP TABLE #tblEntries

IF(OBJECT_ID('tempdb..#tblQuestions')) IS NOT NULL
DROP TABLE #tblQuestions

DECLARE @quizEntriesCount INT
DECLARE @quizQuestionsCount  INT

SET @quizEntriesCount = (SELECT COUNT(1) FROM quiz_entries)
SET @quizQuestionsCount  =(SELECT COUNT(1) FROM quiz_questions)

SELECT @quizEntriesCount AS quizEntriesCount, @quizQuestionsCount  AS quizQuestionCount,

Upvotes: 0

forpas
forpas

Reputation: 164214

Use each query as an expression that returns the number of rows in each table in a SELECT statement: 

SELECT
  (SELECT COUNT(*) FROM quiz_entries) as totalEntries, 
  (SELECT COUNT(*) FROM quiz_questions) as totalQuestions

Upvotes: 1

Related Questions