How to create SQL view with Count()

Question

This is my query:

select 
    Sales.SaleID,
    Sales.StartSaleDate,
    Sales.EndSaleDate,
    Sales.SalePercent,
    COUNT(LessonID) as TotalLesson,
    Sales.Status,
    Sales.ExpiredStatus,
    Sales.SalePrice,
    Sales.IsSpecial
FROM 
    Sales
LEFT JOIN 
    SaleLessons ON SaleLessons.SaleID = Sales.SaleID 
GROUP BY
    Sales.Status, Sales.IsSpecial, Sales.StartSaleDate, Sales.EndSaleDate,
    Sales.SalePercent, Sales.SaleID, Sales.ExpiredStatus, Sales.SalePrice 
ORDER BY 
    Sales.StartSaleDate DESC

Which DBMS are you using? Postgres? Oracle? – Feb 09 '17 at 07:21 — , Feb 09 '17 at 07:21

score 1 · Accepted Answer · edited May 23 '17 at 12:02

    create view ViewSchema.ViewName
    as
select Sales.SaleID,
       Sales.StartSaleDate,
       Sales.EndSaleDate,
       Sales.SalePercent,
       COUNT(LessonID) as TotalLesson,
       Sales.Status,
       Sales.ExpiredStatus,
       Sales.SalePrice,
       Sales.IsSpecial
from Sales
LEFT JOIN SaleLessons
    ON SaleLessons.SaleID = Sales.SaleID 
group by Sales.Status,
         Sales.IsSpecial,
         Sales.StartSaleDate,
         Sales.EndSaleDate,
         Sales.SalePercent,
         Sales.SaleID,
         Sales.ExpiredStatus,
         Sales.SalePrice

You really don't need the ORDER BY clause, you can use it later when extracting data from the view.

Also, here is a very informative answer on this subject - https://stackoverflow.com/a/15188437/7119478

How to create SQL view with Count()

1 Answers1