We help IT Professionals succeed at work.

Syntax for 2 select statements

sl1nger
sl1nger asked
on
215 Views
Last Modified: 2012-05-05
I need to make 1 select statement from these 2 statements-

select page_name, count(*) as Usage from masspricing_log where page_name = 'standard' group by page_name

select page_name, count(*) as Usage from masspricing_log where page_name = 'mass' group by page_name
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
SELECT page_name, count(*)
FROM usage
WHERE page_name in( 'standard', 'mass')
GROUP BY page_name

Commented:
This will return the page name and a count of every row from the usage table where the page name is equal to standard or the page name is equal to mass.

Commented:
SELECT
page_name,
(select count(*) from masspricing_log where page_name = 'standard' group by page_name) as Standard,
(select count(*) from masspricing_log where page_name = 'mass' group by page_name) as Mass
FROM masspricing_log

Author

Commented:
ptjcb - It works in my sql editor, but when I put in my asp page it breaks with this error:  System.Data.SqlClient.SqlException: Incorrect syntax near ')'.

Any tips?

Commented:
There is only the (*) in count, I doubt that that would have an affect. Could you show us more info from the asp code?

Author

Commented:
strQuery = "SELECT page_name, count(*) as Usage FROM(masspricing_log)" & _
 "WHERE page_name = 'standard' OR page_name = 'mass' GROUP BY page_name"

Commented:
strQuery = "SELECT page_name, count(*) as Usage FROM(masspricing_log)" & _
 "WHERE page_name = 'standard' OR page_name = 'mass' GROUP BY page_name"

***
strQuery = "SELECT page_name, count(*) as Usage FROM masspricing_log" & _
 "WHERE page_name = 'standard' OR page_name = 'mass' GROUP BY page_name"

You don't need the parenthesis around the table name.

Author

Commented:
When I run a simple select statement it works fine, but when I use the above I get this error

Incorrect syntax near ')'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ')'.

Source Error:
Line 92:
Line 93:             da.Fill(table)

 

Author

Commented:
now a different error when I removed the () around my table

Incorrect syntax near '='.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '='.

Source Error:

Line 92:
Line 93:             da.Fill(table)

 

Commented:
You may need to add escape quotes around 'Standard' and 'Mass'.

I apologize but I'm not an ASP developer, so this is out of my range.


strQuery = "SELECT page_name, count(*) as Usage FROM masspricing_log" & _
 "WHERE page_name = "'standard'" OR page_name = "'mass'" GROUP BY page_name"
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.