• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

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
0
nkewney
Asked:
nkewney
1 Solution
 
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
 
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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
 
Guy Hengel [angelIII / a3]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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now