We help IT Professionals succeed at work.

SQL subquery problem

376 Views
Last Modified: 2012-05-05
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
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Commented:
try

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

Commented:
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

Author

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?
Jason EvansSenior Software Developer

Commented:
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]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

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

Author

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
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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.