-3

Table:

person | ethnicity

 james | white
  bill | african
  Lina | african
  Ruby | Latin
  josh | Latin

SQL query 1:

SELECT * FROM 
(select count(*) from MyTable where ethnicity = 'white') sub,
(select count(*) from MyTable where ethnicity = 'Latin') sub2

SQL query 2:

SELECT COUNT(ethnicity) as ehtCount, ethnicity
FROM MyTable
GROUP BY ethnicity

The result would be same in both cases. Which SQL is faster?

5
  • @ShreePool the link you referred, I have already visited it but it deals with some different things, although similar but not identical. – user1451111 Nov 23 '17 at 14:44
  • 3
    Each query returns the result in a different format, so what's the point of asking which is faster? Will you use the faster query if it produces the wrong output? – Andriy M Nov 23 '17 at 14:51
  • @AndriyM what do you mean by different format. Both queries prints the count of a certain race, present in the table. – user1451111 Nov 24 '17 at 7:30
  • 1
    The first query will return* two counts in two columns of a single row. The second query will return three rows (for your example), with one count per row. Thus, the results will be presented differently in the output – that's what I meant by "different format". (*Note: actually, the first query won't even compile until you assign a column name for each count(*). I'm guessing you didn't even try it, otherwise you wouldn't need to ask me because you would see for yourself what I meant by my first comment.) – Andriy M Nov 24 '17 at 8:44
0

Group by. Else you have to recreate your stuff when you have more to group by.

Good example: Imagine you had subs for Gender: Now you can "identify" as a car...

Also, your sub example was sub optimal>

SELECT SUM(1) Total, 
SUM(CASE WHEN Race = 'Avacado' THEN 1 else 0 END) Avos, 
SUM(CASE WHEN Race = 'PeachFuzz' THEN 1 ELSE 0 END) Peaches
FROM (SELECT Race FROM [SOURCE])

This allows 1 run through the table... yours had 1 run per Race to sum. Which again shows why group by is better.

4
  • what if i don't want all the ethnicity, instead just want two of them with some additional WHERE clause. For example the count of only white and Latin persons having age greater than 40 and salary less than 500? Do I have any other option in this case than to use subqueries? – user1451111 Nov 23 '17 at 14:24
  • 1
    right, so, try the sum case when criteria is true = 1 else 0 end route... can get messy with complicated logical baskets (was a male, then realised at 40 identified as little girl but subsequently realised that Peter Pan was who he really was...) – Alocyte Nov 23 '17 at 14:31
  • 1
    What is this query? An improvement for the Op's 1st query? Why do you have FROM (SELECT Race FROM [SOURCE]) and not just FROM [SOURCE]? SQL Server's optimizer is clever enough to understand the two are equivalent but why choose the more complex? – ypercubeᵀᴹ Nov 23 '17 at 16:15
  • @Ypercube - yep - didn't need the subquery for "FROM". Thanks. – Alocyte Nov 24 '17 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.