CODEWITHSUNDEEP
mysqlsubquery
Matthew Underwood
Matthew Underwood

Reputation: 1499

Need help on subqueries with LIMIT

I need the last three scores and totals for each standard id where user id = x. Once I had got this I then find out the percentage for each score / total. Then I do a average of the percentage, this percentage is the final percentage for that standard id. The problem I am having is trying to output a final percentage for each standard id. I am able to do one but not sure how to construct the query to perform the rest of the rows.

Getting results for a single standard id

SELECT sto_standard_id, avg((sto_count / sto_total) * 100)
FROM (
SELECT so.sto_standard_id,so.sto_count ,so.sto_total  
FROM mydb.sto_stat_overall as so 
WHERE sto_user_id = 1 
AND so.sto_standard_id = 4
ORDER BY sto_id 
DESC LIMIT 3) as sub

This works,

My attempt at trying the same but for multiple standard ids which dosent work.

SELECT sto_standard_id, avg((sto_count / sto_total) * 100)
FROM (
SELECT so.sto_standard_id,so.sto_count ,so.sto_total  
FROM mydb.sto_stat_overall as so 
WHERE so.sto_standard_id IN (SELECT DISTINCT sto_standard_id FROM mydb.sto_stat_overall WHERE sto_user_id = 1)
ORDER BY sto_id 
DESC LIMIT 3) as sub

It still only returns one row.

Here is some data to play with. 

    DROP TABLE IF EXISTS `sto_stat_overall`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `sto_stat_overall` (
      `sto_id` bigint(20) NOT NULL AUTO_INCREMENT,

  `sto_transaction_id` bigint(20) DEFAULT NULL,
  `sto_user_id` bigint(20) unsigned NOT NULL,
  `sto_standard_id` bigint(20) DEFAULT NULL,
  `sto_count` mediumint(9) DEFAULT NULL,
  `sto_total` mediumint(9) DEFAULT NULL,
  PRIMARY KEY (`sto_id`),
  KEY `transaction_id` (`sto_transaction_id`),
  KEY `user_standard` (`sto_user_id`,`sto_standard_id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

LOCK TABLES `sto_stat_overall` WRITE;
/*!40000 ALTER TABLE `sto_stat_overall` DISABLE KEYS */;
INSERT INTO `sto_stat_overall` VALUES (1,1,1,1,1,1),(2,1,1,2,0,1),(3,2,1,3,1,2),(4,2,1,4,0,2),(5,10,1,6,1,1),(6,10,1,4,0,1),(7,10,1,119,0,1),(8,10,1,3,1,1),(9,10,1,1,1,1),(10,11,1,4,1,1),(11,11,1,10,0,1),(12,11,1,3,0,1),(13,11,1,1,1,1),(14,11,1,119,0,1),(15,32,1,1,1,1),(16,32,1,2,0,1),(17,33,1,1,1,1),(18,33,1,2,0,1);

Upvotes: 1

Views: 35

Answers (1)

seahawk
seahawk

Reputation: 1912

select sto_standard_id, avg((sto_count / sto_total) * 100) from (
select @rank:=if(@prev_cat=sto_standard_id ,@rank+1,1) as rank
  ,so.sto_standard_id,so.sto_count ,so.sto_total,
  @prev_cat:=sto_standard_id 

from sto_stat_overall so,(select @rank:=0, @prev_cat:="")t
  WHERE sto_user_id = 1
order by sto_standard_id , sto_id  desc
  ) temp
  where temp.rank<=3
  group by sto_standard_id

Upvotes: 1

Related Questions