Question:
I have a Visual Basic project about a video store, for which I have to produce a report that includes the following:
- Top 10 movies last month
- Number of new customer accounts during last month
- Revenue the store had last month
Can you please help me with the above three statements?
Answer:
For the first, you can try something like this:
SELECT MovieNameFROM Movies;GROUP BY moviename HAVING COUNT(moviename)>=10
or you can just try SET ROWCOUNT to 10. For the second, try this:
SELECT COUNT(customer_id)FROM customersWHERE start_date > getdate()-30
Actually, you will have to play with this one if you want the month name. For example, use the CONVERT function.
Finally, you can use this for the third item:
SELECT SUM(sales) FROM storesGROUP BY store
When you get a moment, read up and study about aggregates and the group by expression.
