Link to home
Start Free TrialLog in
Avatar of spartasystems
spartasystems

asked on

SQL Count Query in SELECT Statement

Good afternoon all,

I am trying to form a sql select statement that will match a representative to a publication, then count how many adverts they have sold.

I am using 3 tables:

Pub (Holds publication data including Pub_c_PubId)
Advert (Holds details of an advert and stores the Pub_c_PubId as advert_publicationid, it also stores the rep as advert_rep)
ExRep (Holds the Representatives Data such as Rep_Rep, Rep_PublicationId)

Now, If I am looking at a publication I'd like to be able to see which reps are assigned to it:

ThisPublication = Request.QueryString("Pub_c_PubId");

SELECT * FROM ExReps WHERE ExReps.Reps_PublicationID="+ThisPublication;

This is fine and works well, although I'll remove the * and replace with specific fields to reduce query time.

I also would like to see how many adverts each representative has sold:

SELECT COUNT(*) AS NoOfAds FROM Advert WHERE Advert.Advert_PublicationId="+ThisPublication; (I also need to filter by rep here?)

Now I've though about UNION but the tables hold different data so that won't work, I thought about JOIN but cannot seem to structure the query correctly.

It's almost as if I need to run the first query to get the reps, then using that, calculate how many ads they sold.

Would a WHERE EXISTS help with this?

Here is what I have tried: I hope you can see and understand what I'm trying to achieve.
ThisPublication = Request.QueryString("Pub_c_PubId");
 
query = "SELECT x.Reps_Rep, x.Reps_Assignmentdate, x.Reps_Assignmentstatus, COUNT(advert_advertid) AS NoOfAds FROM Advert a, ExReps x WHERE a.advert_publicationid="+ThisPublication+" AND a.advert_rep=e.reps_rep AND Reps_Rep IN (SELECT * FROM ExReps e WHERE e.reps_publicationid="+ThisPublication+");
 
//This errors saying: ExReps.Reps_Rep is invalid in the select list because it is not contained in either an aggregate
//function or the Group By clause

Open in new window

Avatar of Muhammad Kashif
Muhammad Kashif
Flag of Pakistan image

use the group by clause where it shows the error message.

query = "SELECT x.Reps_Rep, x.Reps_Assignmentdate, x.Reps_Assignmentstatus, COUNT(advert_advertid) AS NoOfAds FROM Advert a, ExReps x WHERE a.advert_publicationid="+ThisPublication+" AND a.advert_rep=e.reps_rep AND Reps_Rep IN (SELECT * FROM ExReps e WHERE e.reps_publicationid="+ThisPublication+")
group by x.Reps_Rep, x.Reps_Assignmentdate, x.Reps_Assignmentstatus;
HI

Try this

replace the variables between "<>" with your values
select <your fields> , count(*) as val
from ExReps,Advert
where
Advert.Advert_PublicationId = ExReps.Reps_PublicationID and
ExReps.Reps_PublicationID=<ThisPublication>
group by ExReps.Rep_Rep

Open in new window

Avatar of spartasystems
spartasystems

ASKER

ok, I didn't get an error but had to replace the SELECT * with SELECT e.Reps_Rep

But now it brings up no records.  So maybe the statement isn't doing what I thought it should be doing! lol

Any help would be great in getting it sorted out because I've spent an entire day trying to get this statement to work.

Thanks MuhammadKashif for your swift reply.


SELECT x.Reps_Rep, x.Reps_Assignmentdate, x.Reps_Assignmentstatus,(select  COUNT(advert_advertid) FROM Advert a where {add condition here} ) AS NoOfAds  , ExReps x WHERE a.advert_publicationid="+ThisPublication+" AND a.advert_rep=e.reps_rep AND Reps_Rep IN (SELECT * FROM ExReps e WHERE e.reps_publicationid="+ThisPublication+");
ok, I've got elimesika and JonMny's solutions to try out, I'll get back to you a.s.a.p.

Thank you all.
ok, JonMny:

I tried the above code and had to remove the comma just after the ' AS NoOfAds' and put in ' FROM' instead (I think that's right?)

But I get the error:

'An expression of non-boolean type specified in a context where a condition is expected, near ' , '

So I think I need ' WHERE EXISTS' somewhere.

Thank you for your help, once again.
elimesika:

I can see what's trying to happen but what if the rep has not sold an advert, then the query returns false even though they are assigned.

I'd still need to see them but obviously with zero ads sold.

Thank you.
OK, try this ....
select <your fields> , count(*) as val
from ExReps
LEFT OUTER JOIN Advert
ON Advert.Advert_PublicationId = ExReps.Reps_PublicationID
where
ExReps.Reps_PublicationID=<ThisPublication>
group by ExReps.Rep_Rep

Open in new window

ok, heres what I need to do:

Run a SQL statement to get the reps assigned:

SELECT * FROM ExReps WHERE Reps.PublicationId="+ThisPublication; -- OUTER QUERY

Then using this information, i.e WHERE EXISTS, count the number of ads from the Advert table:

SELECT count(advert_advertid) from Advert WHERE Advert_Publication="+ThisPublication+" AND Advert_Rep= -- INNER QUERY

So each time the outer query runs, it pulls a rep, then the inner query runs to count the ads, then it loops again.

It's like I need a WHERE statement followed by a WHERE EXISTS, but that's incorrect syntax I think?

elimesika:

Does that count not count the Rep records rather than the Advert records? Sorry If I'm being stupid! lol
This should work just make sure that this part
(select  COUNT(advert_advertid) FROM Advert a WHERE Advert_Publication="+ThisPublication+"  ) AS NoOfAds
 
is a valid count query for your rep.


SELECT x.Reps_Rep,
       x.Reps_Assignmentdate,
       x.Reps_Assignmentstatus,
       (select  COUNT(advert_advertid) FROM Advert a WHERE Advert_Publication="+ThisPublication+"  ) AS NoOfAds
       from ExReps x
       WHERE a.advert_publicationid="+ThisPublication+" AND a.advert_rep=e.reps_rep AND Reps_Rep IN (SELECT * FROM ExReps e WHERE e.reps_publicationid="+ThisPublication+");

Thanks JonMny for your response.

I get the following error:

The multi-part identifyer "a.advert_publicationid" could not be bound

The Count I want to perform should be the number of records from the advert table where the rep matches and the publication matches, is this valid?
can you post the query that you tried
Here is the query:
repList.SelectSql = "SELECT x.reps_rep, x.reps_assignmentdate, x.reps_assignmentstatus, (SELECT COUNT(advert_advertid) FROM Advert a WHERE advert_publicationid="+ThisPublication+") AS NoOfAds FROM ExReps x WHERE advert.advert_publicationid="+ThisPublication+" AND advert.advert_rep=e.reps_rep AND reps_rep IN (SELECT * FROM ExReps e WHERE e.reps_publicationid="+ThisPublication+")";

Open in new window

Sorry, should have used carriage returns!
This should be better:
repList.SelectSql = "SELECT x.reps_rep, x.reps_assignmentdate, x.reps_assignmentstatus, (SELECT COUNT(advert_advertid) 
FROM Advert a 
WHERE advert_publicationid="+ThisPublication+") AS NoOfAds 
FROM ExReps x 
WHERE advert.advert_publicationid="+ThisPublication+" 
AND advert.advert_rep=e.reps_rep 
AND reps_rep IN (SELECT * FROM ExReps e WHERE e.reps_publicationid="+ThisPublication+")";

Open in new window

Sorry, I have posted what I tried as a fix (substituting advert for a):


repList.SelectSql = "SELECT x.reps_rep, x.reps_assignmentdate, x.reps_assignmentstatus, 
(SELECT COUNT(advert_advertid) 
FROM Advert a 
WHERE advert_publicationid="+ThisPublication+") AS NoOfAds 
FROM ExReps x 
WHERE a.advert_publicationid="+ThisPublication+" 
AND a.advert_rep=e.reps_rep 
AND reps_rep IN (SELECT * FROM ExReps e WHERE e.reps_publicationid="+ThisPublication+")";

Open in new window

try this


repList.SelectSql = "SELECT x.reps_rep, x.reps_assignmentdate, x.reps_assignmentstatus,
(SELECT COUNT(advert_advertid)
FROM Advert
WHERE advert_publicationid="+ThisPublication+") AS NoOfAds
FROM ExReps x
WHERE a.advert_publicationid="+ThisPublication+"
AND a.advert_rep=e.reps_rep
AND reps_rep IN (SELECT * FROM ExReps e WHERE e.reps_publicationid="+ThisPublication+")";
sorry I just realized that my post will not work, you will want to create a count query and left join
here is an example that I have used that is like what you are trying to do.
with abc(userid,au) as
(
select userid,COUNT(au) from AuExceptions group by  userid
)
select * from Employee e
left Join abc a on a.userid=e.userID
ok a few things here:

Line 7 of the code above, e.reps_rep, should that be x.reps_rep?

We only reference the Advert Table from within the count select statement, does that mean the alias ' a ' is not available to the rest of the query, and therefore it cannot be bound?

I've change both of the above and have a new error:

only one expression can be specified in the select list when the subquery is not introduced with EXISTS

So, In English to me that means:

1.Query the ExReps table and get a list of reps that have the same publicationid number as the publication I'm looking at.  

2.Then if they exist, perform a subquery to count the number of ads from the advert table that match both the rep and the publication.

It sound's so simple when writen like that, but putting it into a query is a nightmare!

JonMny, I thank you for your consistant help....
Please excuse my stupidity, but how do I get that WITH statement into the query?
I should have asked, but what version of sql server are you using?
SQL Server 2005
ASKER CERTIFIED SOLUTION
Avatar of JonMny
JonMny

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm writing this from within an ASP Page, the error I get is that it does not like the fact it starts with ' With'.

I think I'm going to have to admit defeat and think of some other way to get these values to appear on screen.

I think the trouble may be that I'm using a list block that has it's own rules and is strict in what we can do, rather than free-form sql where we could do anything.

I'm going to try everything above again, but without the limitation of the list block to see what happens before declaring defeat! lol

Either way, if it works or not, I'll award the points for shear effort JonMny.
how about this

SELECT x.reps_rep, x.reps_assignmentdate, x.reps_assignmentstatus,
WHERE advert_publicationid="+ThisPublication+") AS NoOfAds
FROM ExReps x
WHERE a.advert_publicationid="+ThisPublication+"
AND a.advert_rep=e.reps_rep
AND reps_rep IN (SELECT * FROM ExReps e WHERE e.reps_publicationid="+ThisPublication+")
left Join (select count(advert_advertid)  , advert_publicationid  group by advert_publicationid)  c
c on c.publicationId =e.reps_publicationid



Unfortunately, the list block, as suspected, only has certain functions allowed with a built in function to AddGridCol, which means it must exist as a true field for me to add it to the list rather than an alias which it erros on.

I'll contact the creator of the software and hopefully their development buys can either offer a solution, or tell me once and for all that it cannot be done.

Thank you for your efforts JonMny, I'll give you the points for your efforts.
Although not true solution was found, the shear effort to help me justifies the points.