CODEWITHSUNDEEP
phpmysqlsumwhere-clause
ShoeLace1291
ShoeLace1291

Reputation: 4698

How do I use SUM in a mysql where clause?

I would like to get results based on the sum of a column in my query. I have two tables: league_squads and league_experience. I want to select squad results that have experience.

The query below gets all the squads on a ladder and orders them in descending order by their total experience. I would like to modify it so that it only gets squads that actually have experience.

$query_squads = "
            SELECT
                s.squad_id AS id, s.ladder_id, s.team_id AS team_id,
                x.experience_id, x.squad_id, SUM(x.value) as total_exp
            FROM league_squads AS s
            LEFT JOIN league_experience AS x ON (s.squad_id = x.squad_id)
            WHERE s.ladder_id = ".$ladder_id."
            ORDER BY total_exp DESC
            ";

If I use the query below, I get an error saying that the column 'total_exp' does not exist.

$query_squads = "
            SELECT
                s.squad_id AS id, s.ladder_id, s.team_id AS team_id,
                x.experience_id, x.squad_id, SUM(x.value) as total_exp
            FROM league_squads AS s
            LEFT JOIN league_experience AS x ON (s.squad_id = x.squad_id)
            WHERE s.ladder_id = ".$ladder_id."
                 AND total_exp > 0
            ORDER BY total_exp DESC
            ";

Here are my tables:

CREATE TABLE IF NOT EXISTS `league_experience` (
  `experience_id` int(15) NOT NULL,
  `squad_id` int(15) NOT NULL,
  `value` int(15) NOT NULL,
  `date_earned` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY (`experience_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `league_squads`
--

CREATE TABLE IF NOT EXISTS `league_squads` (
  `squad_id` int(15) NOT NULL AUTO_INCREMENT,
  `team_id` int(15) NOT NULL,
  `ladder_id` int(15) NOT NULL,
  `date_joined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` tinyint(1) NOT NULL,
  `last_rank` tinyint(5) NOT NULL,
  PRIMARY KEY (`squad_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Upvotes: 1

Views: 3685

Answers (2)

Mark Baker
Mark Baker

Reputation: 212412

SELECT s.squad_id AS id, 
       s.ladder_id, 
       s.team_id AS team_id,
       x.experience_id, 
       x.squad_id, 
       SUM(x.value) as total_exp
  FROM league_squads AS s
  LEFT JOIN league_experience AS x 
         ON (s.squad_id = x.squad_id)
 WHERE s.ladder_id = ".$ladder_id."
HAVING total_exp > 0
 ORDER BY total_exp DESC

Upvotes: 1

Benoît
Benoît

Reputation: 602

You have to make a query like this

SELECT s.squad_id AS id, s.ladder_id, s.team_id AS team_id,
       x.experience_id, x.squad_id, SUM(x.value) as total_exp
FROM league_squads AS s
LEFT JOIN league_experience AS x ON (s.squad_id = x.squad_id)
WHERE s.ladder_id = ".$ladder_id."
HAVING SUM(x.value) > 0
ORDER BY total_exp DESC

Upvotes: 2

Related Questions