{MYSQL}How to get a row, using COUNT(*), to tell you how many times a number has been used

Question

This is my code:

SELECT DATEDIFF(shippedDate, orderDate) AS '# Days', COUNT(*) 
FROM datenumtest2 
GROUP BY '# Days' 
ORDER BY '# Days'

the results give me:

In the # Days, there are 3 number 5's. I would like the output in my code to be

Days: 5   COUNT(*): 3

Any help is greatly appreciated

ScaisEdge · Accepted Answer · 2017-04-03T17:00:52.243

1

in mysql you should use backtics for column name and not quote

SELECT DATEDIFF(shippedDate, orderDate) AS `# Days`, COUNT(*) 
FROM datenumtest2 
GROUP BY `# Days`
ORDER BY `# Days`

or simple days

SELECT DATEDIFF(shippedDate, orderDate) AS Days, COUNT(*) 
FROM datenumtest2 
GROUP BY Days
ORDER BY Days

edited Apr 03 '17 at 17:00

answered Apr 03 '17 at 16:59

ScaisEdge

131,976
10
91
107

@JorgeCampos ..many thanks ...but for double quotes I seem to remember that functions as a single quote .. takea look at http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql – ScaisEdge Apr 03 '17 at 17:05
Thanks so much! Its working just like I needed it to. – Sea W Apr 03 '17 at 17:53
Single quotes is for string literals (SQL ANSI), backticks is mysql specific while double quotes is also SQL ANSI to define delimiters (fields names, etc.). I happen to have an online link of spec of the [SQL Ansi version 99](http://web.cecs.pdx.edu/~len/sql1999.pdf) take a look at the page at the page 129 in the definition of A regular identifier (item 26) :) – Jorge Campos Apr 04 '17 at 01:07

score 0 · Answer 2 · answered Apr 03 '17 at 17:01

It should work that you pass in the "# Days" alias to the count() function: count(`# Days`)should do the trick. Be careful with your alias, as a note: treat it with the same formatting principles as column names. no_days,days_diff,or just days should do the trick.

{MYSQL}How to get a row, using COUNT(*), to tell you how many times a number has been used

2 Answers2