CODEWITHSUNDEEP
sqlsql-serverselect
Baldie47
Baldie47

Reputation: 1254

Select and group by by fields forcing to group by but then not grouping properly

I've been working with this query, but I can't understand why is not grouping properly (showing only combinations of company and zip. but forcing me to group by email as well, how can I do so it groups by company and zip only? 

 SELECT DISTINCT(o.Company), 
       o.Zip, 
       o.Email,
       --RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) AS Domain, 
       cv.EmailDomain, 
       COUNT(o.OrderNumber) AS ordernumber, 
       YEAR(o.OrderDate) AS TheYear, 
       FORMAT(cv.DateVisited, 'MM-yyyy') AS [DateVisited], 
       cv.MeetingType
FROM dbo.CustomerVisits cv
     JOIN dbo.Orders o ON o.Zip = cv.Zip
WHERE(RIGHT(o.Email, LEN(o.Email) - CHARINDEX('@', o.email))) = cv.EmailDomain
GROUP BY o.Zip, 
         o.Email, 
         o.Company, 
         cv.EmailDomain, 
         o.OrderDate, 
         FORMAT(cv.DateVisited, 'MM-yyyy'), 
         cv.MeetingType;

right now is giving this as result:

enter image description here

and giving 260+ rows when the "customervisits" table has only 30+ rows I've been trying to reduce fields in the select, but I need them to be able to filter and pivot later (plus I need to do some manipulation on the select still)

Upvotes: 0

Views: 32

Answers (1)

CR7SMS
CR7SMS

Reputation: 2584

If you group by company and zip, your data will not allow duplicates at that level. If you are fine with using any email for the company and zip, then:

 SELECT o.Company, 
       o.Zip, 
       max(o.Email) as Email,
       max(cv.EmailDomain) as EmailDomain, 
       COUNT(o.OrderNumber) AS ordernumber, 
       YEAR(max(o.OrderDate)) AS TheYear, 
       FORMAT(max(cv.DateVisited), 'MM-yyyy') AS [DateVisited], 
       max(cv.MeetingType) as MeetingType
FROM dbo.CustomerVisits cv
     JOIN dbo.Orders o ON o.Zip = cv.Zip
WHERE(RIGHT(o.Email, LEN(o.Email) - CHARINDEX('@', o.email))) = cv.EmailDomain
GROUP BY o.Zip, 
         o.Company

The above statement will pick the maximum value for all the fields if there are multiple values. Hope this helps.

Upvotes: 1

Related Questions