SQL select and group by help

[seabubble]

Hi, This is the first time I have used the SQL forum.
At the moment my SQL looks like this

(select distinct count("AST_Asset"."Asset_ID_")
from
AST_Asset
where
"AST_Asset"."Type" = 'ROUTER'
and
("AST_Asset"."Status" = '6' or "AST_Asset"."Status" = '0') )

this gives me a figure of 1034. I would like to group by region but am having trouble. I have been given this SQL but do not understand the way it is ment to work. I get the following error message. The column prefix AST_Asset does not match the the table name or alias name used in the query. I would like it to show me the assets for each region and group them by region. Can you help please?

(select distinct count("AKA"."Asset_ID_")
from AST_Asset AKA
where "AKA"."Region" = "AST_Asset"."Region"
and
"AKA"."Type" = 'ROUTER')

 

[dbomrrsm]

any help ?

select distinct count(Asset_ID_) ,Region
from
AST_Asset
where Type = 'ROUTER'
and
(Status = '6' or Status = '0') 
GROUP BY Region

[blue]DBomrrsm[/blue]

 

[seabubble]

Thanks for getting back DBomrrsm.

I have put the following in
(select distinct count("AST_Asset"."Asset_ID_") , "AST_Asset"."Region"
from
AST_Asset
where
"AST_Asset"."Type" = 'ROUTER'
and
("AST_Asset"."Status" = '6' or "AST_Asset"."Status" = '0') group by "AST_Asset"."Region")

and get "Only one expression can be specified in the select list when the sub query is not introduced with exits" what on earth does that mean?

 

[dbomrrsm]

was wondering why the query started and ended with brackets - just realised - its a sub-query - so now to start to understand why it wont work for you I would need to see the main query - can you post it all ?

[blue]DBomrrsm[/blue]

 

[seabubble]

Sorry !

SELECT "AST_Asset"."Region", ((select distinct count("AST_Asset"."Asset_ID_")
from
AST_Asset
where
"AST_Asset"."Type" = 'ROUTER'
and
("AST_Asset"."Status" = '6' or "AST_Asset"."Status" = '0') )

)
FROM "ARSystem"."dbo"."AST_Asset" "AST_Asset"
WHERE NOT ("AST_Asset"."Region"=N'GSS' OR "AST_Asset"."Region"=N'HELPDESK' OR "AST_Asset"."Region"=N'I2N' OR "AST_Asset"."Region"=N'NPD')
ORDER BY "AST_Asset"."Region"

 

[seabubble]

I think that Crystal reports puts the added SQL expression into the main SQL, looks like that anyway.

 

[dbomrrsm]

try thuis:

 SELECT "AST_Asset"."Region", ((select distinct count("AST_Asset"."Asset_ID_") 
from
AST_Asset
where
"AST_Asset"."Type" = 'ROUTER'
and
("AST_Asset"."Status" = '6' or "AST_Asset"."Status" = '0') )

)
 FROM   "ARSystem"."dbo"."AST_Asset"  [COLOR=#ff0000]"AST_Asset"[/color]
 WHERE   NOT ("AST_Asset"."Region"=N'GSS' OR "AST_Asset"."Region"=N'HELPDESK' OR "AST_Asset"."Region"=N'I2N' OR "AST_Asset"."Region"=N'NPD')
 GROUP BY "AST_Asset"."Region"
 ORDER BY "AST_Asset"."Region"

Paste any error messages you get - if any.

With you moving the Region outside of the sub-query you are now only specifying one expression so it could now work. If it doesnt try deleting the bit I have highlighted in red and running it again before posting error massages.

[blue]DBomrrsm[/blue]

 

[seabubble]

Hi again, First it said that there was a syntax error near select. That was the ( missing. Then when I fixed that it said that there was a syntax error near from, but did not say which from. I also took the red text out and put "ARSystem"."dbo"."AST_Asset" at the top
(SELECT "AST_Asset"."Region", ((select distinct count("AST_Asset"."Asset_ID_")
from "ARSystem"."dbo"."AST_Asset"
where
"AST_Asset"."Type" = 'ROUTER'
and
("AST_Asset"."Status" = '6' or "AST_Asset"."Status" = '0') )

)
FROM "ARSystem"."dbo"."AST_Asset" "AST_Asset"
WHERE NOT ("AST_Asset"."Region"=N'GSS' OR "AST_Asset"."Region"=N'HELPDESK' OR "AST_Asset"."Region"=N'I2N' OR "AST_Asset"."Region"=N'NPD')
GROUP BY "AST_Asset"."Region"
ORDER BY "AST_Asset"."Region"

still getting incorrect syntax near from. have tried

 

[dbomrrsm]

If you double click the error message in the bottom pane of QA it will highlight the line where the error message is - so this should tell you which from the error is being caused by - let me know if you cant see the reason why yourself. Paste the error line in italics of something.

[blue]DBomrrsm[/blue]

 

[SQLBill]

You say you are using Crystal Reports for the front end. Are you using Microsoft SQL Server for the backend? If not, you are in the wrong forum. (You are in the wrong forum anyways - there is a Microsoft SQL Server: Programming forum (Forum183) - but don't switch now if you are using SQL Server).

If you aren't using SQL Server, then you need to know that not all SQL is created equal. That may be why you are getting the syntax error.

So, what ARE you using for your database (backend)?

-SQLBill

 

[seabubble]

SQLBill - Backend is SQL server this sql is inside Crystal reports.

DBomrrsm Cant find that part to click on I am inside crystal reports sql expression builder

I have been in a four hour meeting so could not get back sooner. Will have a go but it will be tomorrow as it's late here.

I know I might be in the wrong forum but wanted to know how the sql was working. Thanks to you both I am understanding more. I will be buying an SQL manual Saturday!

 

[SQLBill]

He's talking about testing it inside of SQL Server's Query Analyzer. That's one reason I was asking what backend you were using.

Try the query in SQL Server's Query Analyzer and see if it works.

Instead of a manual, check out the BOL for SQL Server.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[seabubble]

Hi SQLBill. Been really busy, like all of us. This stuff is inside Crystal reports. It is a seperate section you can do extra sql while the report is running. Thanks for the tip about BOL. I will hunt someone down who has the CD as it's not running on this pc as it's on the server.I must get my hands on the sql front end so I can try out the selects away from anything else.

 

[SQLBill]

Since this is on how to do 'extra' SQL in Crystal Reports, you might try posting in the Crystal Reports forums.

Do you not have access to the SQL Server itself?

The problem is that normally you should test the query on SQL Server first. Fix any syntax errors, etc. then deploy it to the front-end system (Crystal Reports in this case). Now you are trying to solve the issue on the front-end only and you can't narrow down the problem to the front-end or the back-end, it's just guesswork as to where the problem is.

-SQLBill