MySQL Count multiple column syntax

Question

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?

hellcode · Accepted Answer · 2014-07-06 16:36:37Z

1

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

answered Jul 6, 2014 at 16:36

hellcode

2,6881 gold badge19 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ronald Over a year ago

Ah yes of course! I totally forgot about GROUP BY. It's been a while! Thanks!

Collectives™ on Stack Overflow

MySQL Count multiple column syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related