Using "as" to label the column of results

Databases
1

Right now I have a part of a query this is just one select of a larger query with many selects, thus the use of the parentheses.

(SELECT COUNT(*) FROM marketing WHERE date(dateClicked) = current_date() - INTERVAL 1 DAY) as marketing

But I want to add " and source = ‘Popup’ " to the WHERE clause. This modification works:

(SELECT COUNT(*) FROM marketing WHERE date(dateClicked) = current_date() - INTERVAL 1 DAY and source = ‘Popup’);

However, if I try to add “as marketing” the query errors out:

(SELECT COUNT(*) FROM marketing WHERE date(dateClicked) = current_date() - INTERVAL 1 DAY and source = ‘Popup’) as marketing;

What am I doing wrong?

2

What’s the error, just a generic “syntax error near” message? Although it’s unlikely, does it work any better as

(SELECT COUNT(*) FROM marketing WHERE date(dateClicked) = (current_date() - INTERVAL 1 DAY) and source = 'Popup') as marketing;

or even with the alias different from the table name?

3

hard to tell without seeing the entire query

that semicolon looks wrong, though

1 Like
4

Are you trying to create an alias for the count value, or for the sub-query/table itself?

If for the count so that it’s available in the result set with a name, then it’s in the wrong place.

SELECT COUNT(*) AS marketing 
  FROM marketing 
 WHERE date(dateClicked) = current_date() - INTERVAL 1 DAY 
   AND source = ‘Popup’

If you’re trying to alias the sub-query/table, we’d need to see the whole query - how you’re showing it makes no sense as it gains you nothing, and a join as written would make no sense because you’d be joining on a specific count, which is treacherous at best…

5

Here is the full query:

SELECT
(SELECT COUNT() FROM members WHERE date(registerDate) = current_date() - INTERVAL 1 DAY) as members,
(SELECT COUNT() FROM logins WHERE date(loginDate) = current_date() - INTERVAL 1 DAY) as logins,
(SELECT COUNT() FROM emails WHERE date(dateSent) = current_date() - INTERVAL 1 DAY) as emails,
(SELECT COUNT() FROM searches WHERE date(searchDate) = current_date() - INTERVAL 1 DAY) as searches,
(SELECT COUNT() FROM views WHERE date(dateAdded) = current_date() - INTERVAL 1 DAY) as views,
(SELECT COUNT() FROM referrals WHERE date(dateAdded) = current_date() - INTERVAL 1 DAY) as referrals,
(SELECT COUNT() FROM favorites WHERE date(dateAdded) = current_date() - INTERVAL 1 DAY) as favorites,
(SELECT COUNT() FROM payments WHERE date(transactionDate) = current_date() - INTERVAL 1 DAY) as payments,
(SELECT COUNT() FROM interactions WHERE date(answerDate) = current_date() - INTERVAL 1 DAY) as interactions,
(SELECT COUNT() FROM marketing WHERE date(dateClicked) = current_date() - INTERVAL 1 DAY) and source = ‘Popup’ as marketing,
(SELECT COUNT() FROM testimonials WHERE date(dateAdded) = current_date() - INTERVAL 1 DAY) as testimonials,
(SELECT COUNT() FROM notesOnline WHERE date(dateAdded) = current_date() - INTERVAL 1 DAY) as notesOnline,
(SELECT COUNT() FROM notesOffline WHERE date(dateAdded) = current_date() - INTERVAL 1 DAY) as notesOffline,
(SELECT COUNT() FROM matches WHERE date(dateScheduled) = current_date() - INTERVAL 1 DAY) as matches,
(SELECT COUNT() FROM messagesOnline WHERE date(dateAdded) = current_date() - INTERVAL 1 DAY) as messagesOnline,
(SELECT COUNT() FROM messagesOffline WHERE date(dateAdded) = current_date() - INTERVAL 1 DAY) as messagesOffline
;

6

No to both of your questions. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘as marketing’ at line 1. Error code 1064.

7

Ah, yes. Seeing the context helps. Look at that line and all the ones before and after it - what do you see that is different? Look specifically at what you added.

Hint
What’s different between this (which would work)

(SELECT COUNT(*) FROM marketing WHERE date(dateClicked) = current_date() - INTERVAL 1 DAY and source = ‘Popup’) as marketing

and what you wrote?

(SELECT COUNT() FROM marketing WHERE date(dateClicked) = current_date() - INTERVAL 1 DAY) and source = ‘Popup’ as marketing,

Use the scrollbars carefully until you see something different :shifty:

8

How odd. This forum removed all of the asterisks from each of my queries when I pasted the big query in. Your first example did not work for me just now when trying it.

9

If you’re saying this query works

(SELECT COUNT(*) FROM marketing WHERE date(dateClicked) = current_date() - INTERVAL 1 DAY) as marketing,

Then this should work. You had the closing parenthesis in the wrong place in the query you posted

(SELECT COUNT(*) FROM marketing WHERE date(dateClicked) = current_date() - INTERVAL 1 DAY and source = ‘Popup’) as marketing,

Only other thing I could think of would be to wrap the date check in parenthesis for some odd reason. If that doesn’t work, are you sure source is a field on the marketing table?

(SELECT COUNT(*) FROM marketing WHERE (date(dateClicked) = current_date() - INTERVAL 1 DAY) and source = ‘Popup’) as marketing,
10

Ah, that’s an artifact of the markdown that discourse uses => * is for italics

But that wasn’t what was wrong with your query :wink:

11

I found the problem. I had the following query pulled out of the big one to refine it, and ending it with a semicolon caused the query to fail, like so:

(SELECT COUNT(*) FROM marketing WHERE date(dateClicked) = current_date() - INTERVAL 1 DAY and source = ‘Popup’) as marketing;

But when I just edited the big query, so that there is a comma after “as marketing”, then the big query returned results. I wonder why the semicolon was troublesome in this case?

12

Just using a bit of the query posted in the first post as an example:

SELECT
	(
		 SELECT
			COUNT(*)
		FROM
			members
		WHERE
			date(registerDate) = current_date() - INTERVAL 1 DAY
	) as members,
	(
		SELECT
			COUNT(*)
		FROM
			logins
		WHERE
			date(loginDate) = current_date() - INTERVAL 1 DAY
	) as logins,

It makes it easier to read

13

Then the query you posted in #5 wasn’t the actual query?
image

which had this (reformatted so you don’t have to scroll)

(SELECT COUNT(*) 
   FROM marketing 
  WHERE date(dateClicked) = current_date() - INTERVAL 1 DAY) 
    AND source = ‘Popup’ as marketing,

that last parenthesis was the problem in the example you posted. It had to be

(SELECT COUNT(*) 
   FROM marketing 
  WHERE date(dateClicked) = current_date() - INTERVAL 1 DAY 
    AND source = ‘Popup’) as marketing
14

Thanks for your help @DaveMaxwell!

1 Like
15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.