-1

I need help in comparing data within the same column. For Eg. I have a Business table which has id, name, address, city, state and stars columns.

The stars column consists the following values : 1.0, 1.5, 2.0, 2.5, 3.0, 3.5, 4.0, 4.5, 5.0.

Now I have to compare the businesses with 2-3 stars to the businesses with 4-5 stars so How do I do that? Any help is appreciated.

Thanks in advance.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
msbeast
  • 45
  • 1
  • 9
  • 2
    Compare what? Their names? Their states? Something else? – LukStorms Nov 19 '18 at 14:18
  • @LukStorms : I need to compare the business on the basis of stars i.e. 2-3 stars with 4-5 stars. – msbeast Nov 19 '18 at 14:20
  • 2
    See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Nov 19 '18 at 14:23
  • 3
    what does "compare" mean, exactly? What should the result of your "comparison" be? You mean you just want them listed in order of their star rating value? Or something else? It's unclear what output you want to see. Please show us an example. – ADyson Nov 19 '18 at 14:25
  • 2
    Also unclear... is this for MySQL or Sqlite? You have both tagged. – Shawn Nov 19 '18 at 14:26
  • 2
    Just show us the expected output. That would really be helpful. – Tim Biegeleisen Nov 19 '18 at 14:26
  • @ADyson : Compare as in I need data for 2-3 Stars and data with 4-5 Stars. I have listed them with their star ratings value but they comes with each stars ratings whereas I need a combined output of 2-3 Stars and 4-5 Stars respectively i.e. How much business done for 2-3 stars and 4-5 stars. – msbeast Nov 19 '18 at 14:36
  • @Shawn : It is for MySQL. – msbeast Nov 19 '18 at 14:36
  • right so you probably want a COUNT or SUM combined with a GROUP BY, depending exactly what statistic you're looking for. We don't know what "business done" means. Seriously, take a look at the link Strawberry gave and provide a complete set of info, so we don't have to drag it out of you one thing at a time, or make answers based on guesswork. Thanks. – ADyson Nov 19 '18 at 14:37
  • @TimBiegeleisen : Sorry but I don't have the expected output. I have just received this question with table structure that I have mentioned above. – msbeast Nov 19 '18 at 14:37
  • If you don't know what the expected output is then how are you planning to test your query and decide that you've got it right?? – ADyson Nov 19 '18 at 14:38
  • 1
    ...and if you don't test your query, then how are you going to know the expected output (hat tip to Pink Floyd's Another Brick in the Wall) :-) – Tim Biegeleisen Nov 19 '18 at 14:38
  • @ADyson : Thank you for your time. I am sorry since I am new to this portal. Going forward will definitely keep these things in mind and provide appropriate details. – msbeast Nov 19 '18 at 14:39
  • No problem, but it's not something specific to this website...if you were describing this to the person sitting next to you, you'd still need to explain everything, and provide details, before they could help you. We await your update to the question :-) – ADyson Nov 19 '18 at 14:43
  • 1
    Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) – Juan Carlos Oropeza Nov 19 '18 at 14:51
  • Thank you @ADyson and Juan. The second query out of the 2 queries provided by LukStorms helped. – msbeast Nov 19 '18 at 15:09

1 Answers1

1

I'm guessing "they" want counts per range of stars.
If so, then you could group on a CASE.

SELECT 
(CASE 
 WHEN stars BETWEEN 0.0 AND 1.5 THEN '0.0-1.5'
 WHEN stars BETWEEN 2.0 AND 3.5 THEN '2.0-3.5'
 WHEN stars BETWEEN 4.0 AND 5.0 THEN '4.0-5.0'
 END) as stars_range,
COUNT(*) AS TotalBusiness,
COUNT(DISTINCT city) AS TotalCities,
COUNT(DISTINCT state) AS TotalStates
FROM Business
WHERE stars BETWEEN 2.0 AND 5.0
GROUP BY 
(CASE 
 WHEN stars BETWEEN 0.0 AND 1.5 THEN '0.0-1.5'
 WHEN stars BETWEEN 2.0 AND 3.5 THEN '2.0-3.5'
 WHEN stars BETWEEN 4.0 AND 5.0 THEN '4.0-5.0'
 END)

Or maybe a conditional aggregate looks better?

SELECT 
COUNT(CASE WHEN stars BETWEEN 2.0 AND 3.5 THEN 1 END) AS stars_2_3,
COUNT(CASE WHEN stars BETWEEN 4.0 AND 5.0 THEN 1 END) AS stars_4_5
FROM Business
WHERE stars BETWEEN 2.0 AND 5.0
LukStorms
  • 28,916
  • 5
  • 31
  • 45