Avatar of mahpog
mahpog
 asked on

Adjust sql syntax for right result set

I have following sql sytnax:

select rptdate as mmddyyyy,
SUM(CASE WHEN box_loc = 'SMCBUYR' THEN 1 ELSE 0 END) AS 'SMCBUYR'
from a_smc_rpt_helper
left join a_smc
on rptdate = date_rcvd
where rptdate between '04/07/2008' and '04/11/2008'
group by rptdate, box_loc
order by rptdate

And the result set is:
mmddyyyy                     SMCBUYR
4/7/2008 12:00:00.000 AM      0
4/8/2008 12:00:00.000 AM      0
4/8/2008 12:00:00.000 AM      2
4/8/2008 12:00:00.000 AM      0
4/8/2008 12:00:00.000 AM      0
4/9/2008 12:00:00.000 AM      0
4/9/2008 12:00:00.000 AM      3
4/9/2008 12:00:00.000 AM      0
4/10/2008 12:00:00.000 AM      0
4/11/2008 12:00:00.000 AM      0

I should only have one entry for 4/08 and 4/09 with 2 and 3 respectively.........
Otherwise result set would be perfect..............

I would have 5 lines output.



SQL

Avatar of undefined
Last Comment
mahpog

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Guy Hengel [angelIII / a3]

to explain, the group by box_loc produced more rows...
mahpog

ASKER
Such a basis adjustment and PERFECT!!!!!!!!! thank you!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck