-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?

Improve this question
5
  • stackoverflow.com/questions/3156090/…
    – ShreePool
    Nov 23, 2017 at 14:08
  • @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, 2017 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, 2017 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, 2017 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, 2017 at 8:44

1 Answer 1

Reset to default
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.

Improve this answer
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, 2017 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, 2017 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, 2017 at 16:15
  • @Ypercube - yep - didn't need the subquery for "FROM". Thanks.
    – Alocyte
    Nov 24, 2017 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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