MYSQL - Issue with sub-query

Question

SELECT DISTINCT(id_no), lastname,
    (SELECT COUNT(purchasedate)  num_of_purch
    FROM sales JOIN Artist ON
        sales.id = Artist.id_no
        WHERE  DATE_SUB(CURDATE(),INTERVAL 1
                          YEAR) <= purchasedate 
              ) AS num_of_purch

FROM Artist

This query returns the all Artist's ID_no, and their last name and the total number of purchases, altho i want to specify which purchases were to which artist. Help in solving this would be greatly apprciated.

EDIT - DISTINCT(id_no) is redundant as it is a primary key.

fthiella · Accepted Answer · 2012-11-27 22:13:19Z

2

This shows the number of sales for each artist_id:

SELECT artist.id_no, count(sales.id) as num_of_purch
FROM artist left join sales on sales.id = artist.id_no
WHERE DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= purchasedate
GROUP BY artist.id

To return also the last names, and all of the details:

SELECT art_tot.id_no, art_tot.lastname, art_tot.num_of_purch, sales.*
FROM (SELECT artist.id_no, artist.lastname, count(sales.id) as num_of_purch
      FROM artist left join sales on sales.id = artist.id_no
      WHERE DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= purchasedate
      GOUP BY artist.id, artist.lastname) art_tot
      left join sales on art_tot.id_no = sales.id

edited Nov 27, 2012 at 22:13

answered Nov 27, 2012 at 21:45

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

fthiella Over a year ago

:) i edited and removed an inner join... it should still work now

user1854392 Over a year ago

Oh quick thing. Isn't 100% what i need. Basically its listing all artists who have had purchases, but i also need artists who haven't had any purchases.

fthiella Over a year ago

i changed last inner join in a left join... this will show all the records from the first subquery, even if they are not correlated to the sales table

user1854392 Over a year ago

which query are you referring to, your first or second? I have taken your first solution and found this flaw.

fthiella Over a year ago

ok sorry i had to update both queries, i just edited again, i think it should be okay now...

Olaf Dietsche · Accepted Answer · 2012-11-27 21:47:33Z

1

This should give you artist and number of purchases per artist

select a.id_no, a.lastname, count(s.purchasedate) num_of_purch
from artists a
join sales s on a.id_no = s.id
where date_sub(curdate(), interval 1 year) <= s.purchasedate
group by a.id_no, a.lastname

answered Nov 27, 2012 at 21:47

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

Comments

Bill Karwin · Accepted Answer · 2012-11-27 21:54:05Z

0

You should use a GROUP BY to get the count per artist.

And you should use an outer join to include artists who have no sales within the last year.

SELECT a.id_no, a.lastname, COUNT(s.purchasedate) AS num_of_purch
FROM Artist a
LEFT OUTER JOIN sales s ON s.id = a.id_no
  AND s.purchasedate => CURDATE() - INTERVAL 1 YEAR
GROUP BY a.id_no;

PS: Using DISTINCT(id_no) is meaningless not only because id_no is already a unique key, but because DISTINCT always applies to all columns in the select list, even if you add parentheses to make it look like a function that applies only to one column.

answered Nov 27, 2012 at 21:54

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

1 Comment

user1854392 Over a year ago

Your solution comes with an error, saying SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=> CURDATE() - INTERVAL 1 YEAR GROUP BY A.id_no' at line 4

Collectives™ on Stack Overflow

MYSQL - Issue with sub-query

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related