SQL subquery problem

Dear Experts,

I'm trying to write a subquery to count the number of sales of a particular product (including those with no sales.

Here's how I get a list of all products

SELECT     BARCODE, TITLE
FROM         Films
ORDER BY TITLE

Here's how I get a list of sales

SELECT     TITLE, Barcode
FROM         Q_DispenserOperation
WHERE     (GUID = '{2249EE46-E7CD-4763-AB23-A8F3C6A5940A}') AND (OpType = 2)

How would I list all the products, and the number of sales in a query.

For example:

FILM | QTY SOLD
Film1 | 1
Film2 | 0

etc.

Thanks in advance

Nick
LVL 1
nkewneyAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
assuming that it is the title column that "mismatches" the collation, the syntax for my suggestion would be this:

SELECT     f.BARCODE, f.TITLE
, (select count(*)
  from Q_DispenserOperation q
  where q.GUID = '{2249EE46-E7CD-4763-AB23-A8F3C6A5940A}'
    AND q.OpType = 2
    and q.barcode = f.barcode
    and q.title = f.title COLLATE <here goes the collation name of q.title>
  ) sales
FROM         Films f
ORDER BY f.TITLE

just put in the collation name the error reported in there as needed
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SELECT     f.BARCODE, f.TITLE
, (select count(*)
  from Q_DispenserOperation q
  where q.GUID = '{2249EE46-E7CD-4763-AB23-A8F3C6A5940A}'
    AND q.OpType = 2
    and q.barcode = f.barcode
    and q.title = f.title
  ) sales
FROM         Films f
ORDER BY f.TITLE

0
 
adymonoCommented:
try

SELECT     TITLE, count(*) as sales
FROM         Q_DispenserOperation
WHERE     (GUID = '{2249EE46-E7CD-4763-AB23-A8F3C6A5940A}') AND (OpType = 2)
group by Barcode
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Bart1981Commented:
try this

select a.title film, (select count(b.barcode) from  Q_DispenserOperation b where b.barcode = a.barcode) qty_sold from films a order by 1

0
 
nkewneyAuthor Commented:
Thanks all

Strange error:

Cannot resolve the collation conflict between Latin_General_CI_AS and SQL_Latin_General_CP1_CI_AS in the equal to operation

Any ideas?
0
 
Jason EvansSenior Software DeveloperCommented:
Hi there.

Try:

SELECT F.Film, Count(Q.Barcode) AS 'Qty'
FROM Films F

INNER JOIN  Q_DispenserOperation Q ON Q.Barcode = F.Barcode

WHERE     (GUID = '{2249EE46-E7CD-4763-AB23-A8F3C6A5940A}') AND (OpType = 2)

GROUP BY F.Barcode

Cheers.
Jas.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Cannot resolve the collation conflict between Latin_General_CI_AS and SQL_Latin_General_CP1_CI_AS in the equal to operation

this means that the 2 tables have different collations on their columns, or that one of them does not have a default (database-wide) collation...
fix that, or use the COLLATE keyword in the query.
0
 
nkewneyAuthor Commented:
Ok - just read up on it. Don't want to change the collations.

Would you be able to give me an example of using collate within the context of this query?

Thanks
Nick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.