CODEWITHSUNDEEP
mysqlcount
Ronald
Ronald

Reputation: 17

MySQL Count multiple column syntax

I have this table:

CREATE TABLE IF NOT EXISTS `voertuiglijnen` 
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `voertuig` int(11) NOT NULL,
  `lijn` text NOT NULL,
  `publijn` int(11) NOT NULL,
  `rit` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

This is some sample input:

|id    |voertuig    |lijn    |publijn    |rit
|------|------------|--------|-----------|-----
|1     |5376        |A060    |60         |1
|2     |5376        |A062    |62         |2
|3     |5376        |A062    |62         |3

I want the SQL result to display each "lijn" with the same "voertuig", and then order it by occurence of "lijn".

SELECT DISTINCT `lijn`, `publijn`, count(*) as aantal 
FROM `voertuiglijnen` 
WHERE `voertuig` = '5376' 
ORDER BY aantal DESC

In this case, it should put A062 as first, and A060 as second. But it's doing it the other way around.

What am I doing wrong here?

Upvotes: 0

Views: 15

Answers (1)

hellcode
hellcode

Reputation: 2698

Use GROUP BY instead of DISTINCT:

SELECT `lijn`, `publijn`, count(*) as aantal
 FROM `voertuiglijnen`
 WHERE `voertuig` = '5376'
 GROUP BY 1,2
 ORDER BY aantal DESC

Upvotes: 1

Related Questions