asked on

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

Guy Hengel [angelIII / a3]

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

adymono

try

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

Bart1981

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

nkewney

ASKER

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?

Jason Evans

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.

Guy Hengel [angelIII / a3]

>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.

nkewney

ASKER

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer