Group By Only One

So here's the code that I'm trying to run

SELECT ttl.end_tran_date,
(CASE
WHEN tim.pick_area = 'CASEMOD' THEN COUNT(DISTINCT ttl.hu_id)
ELSE 0
END) AS [CMod_Location],
(CASE
WHEN tim.pick_area = 'PLTMOD' THEN COUNT(DISTINCT ttl.hu_id_2)
ELSE 0
END) AS [PMod_location]
FROM t_tran_log ttl WITH(NOLOCK)
LEFT OUTER JOIN t_item_master tim WITH(NOLOCK) ON ttl.item_number = tim.item_number
WHERE ttl.tran_type = '306' AND tim.pick_area IN ('CASEMOD','PLTMOD')
GROUP BY ttl.end_tran_date, tim.pick_area
ORDER BY ttl.end_tran_date

The problem is that I can't group by just ttl.end_tran_date, it gives me an error when I do because tim.pick_area isn't group by'd anything.
My results from this query look like this

end_tran_date CMod_Location PMod_location
2019-11-18 00:00:00.000 876 0
2019-11-18 00:00:00.000 0 40

But I want to basically combine it all into the same day with two different columns one for CMod and one for PMod.

Hi

can you please provide sample data !!!
create table insert table script ????

thanks !!! we can look into the SQL after that !!!

My guess is that you need to use MAX on the columns

SELECT ttl.end_tran_date,
MAX(CASE
WHEN tim.pick_area = 'CASEMOD' THEN COUNT(DISTINCT ttl.hu_id)
ELSE 0
END) AS [CMod_Location],
MAX(CASE
WHEN tim.pick_area = 'PLTMOD' THEN COUNT(DISTINCT ttl.hu_id_2)
ELSE 0
END) AS [PMod_location]
FROM t_tran_log ttl WITH(NOLOCK)
LEFT OUTER JOIN t_item_master tim WITH(NOLOCK) ON ttl.item_number = tim.item_number
WHERE ttl.tran_type = '306' AND tim.pick_area IN ('CASEMOD','PLTMOD')
GROUP BY ttl.end_tran_date, tim.pick_area
ORDER BY ttl.end_tran_date

Yup
looks like you are right Madhivanan

I have tested it

please click arrow to the left for SAMPLE data script
drop table #data 
go 
create table #data 
(
end_tran_date date ,
pick_area varchar(10)
)
go 
insert into #data select '2019-10-11','CASEMOD'
insert into #data select '2019-10-11','PLTMOD'
go 

select 'sample data ', * from #data
go

SELECT 'Without Max', 
       end_tran_date,
CASE
	WHEN pick_area = 'CASEMOD' then 100
	ELSE 0
 END AS [CMod_Location],
CASE
	WHEN pick_area = 'PLTMOD' then 200
	ELSE 0
END AS [PMod_location]
from #data
group by end_tran_date
go 

SELECT 'With Max', 
       end_tran_date,
MAX(CASE
	WHEN pick_area = 'CASEMOD' then 100
	ELSE 0
 END) AS [CMod_Location],
MAX(CASE
	WHEN pick_area = 'PLTMOD' then 200
	ELSE 0
END) AS [PMod_location]
from #data
group by end_tran_date
go 

You need to add the tim.pick_area to the select if you are going to group by that value...or remove it from the group by...

I reformatted your query and removed the NOLOCK - you should not use that as it can cause invalid data (and other issues):

 Select ttl.end_tran_date
      , tim.pick_area
      , sum(Case When tim.pick_area = 'CASEMOD' Then 1 Else 0 End) As [CMod_Location]
      , sum(Case When tim.pick_area = 'PLTMOD' Then 1 Else 0 End) As [PMod_location]
   From t_tran_log ttl 
   Left Join t_item_master tim On ttl.item_number = tim.item_number
  Where ttl.tran_type = '306'
    And tim.pick_area In ('CASEMOD', 'PLTMOD')
  Group By
        ttl.end_tran_date
      , tim.pick_area

If you are counting 'duplicates' - you need to figure out why you are getting duplicate rows which is probably due to the outer join...

@harishgg1 @madhivanan
The max didn't work
This is the error code that I've been getting
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
I'm using Microsoft SQL Server.
I also cleaned up the columns to look cleaner for the users this is what I'm using now

SELECT ttl.end_tran_date[Replen Date],
MAX(CASE
WHEN tim.pick_area = 'CASEMOD' THEN COUNT(DISTINCT ttl.hu_id)
ELSE 0
END) as [CMOD],
MAX(CASE
WHEN tim.pick_area = 'PLTMOD' THEN COUNT(DISTINCT ttl.hu_id_2)
ELSE 0
END) AS [PMOD]
FROM t_tran_log ttl with(nolock)
LEFT OUTER JOIN t_item_master tim WITH(NOLOCK) ON ttl.item_number = tim.item_number
WHERE ttl.tran_type = '306' AND tim.pick_area IN ('CASEMOD','PLTMOD')
GROUP BY ttl.end_tran_date

I also tried it exactly as @madhivanan ran it with the tim.pick_area in the group by.
@jeffw8713 Unfortunately I'm not allowed to take the WITH(NOLOCK) off of my queries, the database admin teams from corporate said we have to use with(nolock) on all queries we run otherwise we could crash the system by having people not being able to change the lines we're touching. Also I wanted it as the way that Harishgg1 put it in this screen cap
https://aws1.discourse-cdn.com/business6/uploads/sqlteam/original/2X/4/4b327a1d3e9de19ddb40642b5bec68079a0eacf1.png

Have you added group by clause

Yes, I still have the group by on the query. It's just giving me that error and if I run it without the tim.pick_area and if I do run it with the tim.pick_area (with or without MAX) it gives me the 2 lines when I only want one returned.

That makes a difference

Select column1 , max
Group by column1

Or

Select column1, column2 , max
Group by column1 , column 2

The adding of extra column
Will give
Different results

The reason you are getting 2 rows is because you have included tim.pick_area in the GROUP BY. If you include that column in the SELECT you will see that the reason you have 2 rows is because each row is broken out by the value in that column. Remove that column from the GROUP BY and you will get a single row returned for each ttl.end_tran_date.

With that said - if you just need a count of items in the tran_log that match your criteria and not a count of the number of items that match in item_master you need to change how you JOIN to that table. You may need to use a derived table to select 1 row only...or a derived table to select distinct:

 Select ttl.end_tran_date
      , sum(Case When tim.pick_area = 'CASEMOD' Then 1 Else 0 End) As [CMod_Location]
      , sum(Case When tim.pick_area = 'PLTMOD' Then 1 Else 0 End) As [PMod_location]
   From t_tran_log As ttl with (nolock)
  Inner Join (Select Distinct
                     im.item_number
                   , im.pick_area
                From t_item_master As im with (nolock) 
               Where im.pick_area In ('CASEMOD', 'PLTMOD')
              ) AS tim ON tim.item_number = ttl.item_number
  Where ttl.tran_type = '306'
  Group By
        ttl.end_tran_date;

This will get you a 'count' of the matching number of items...

1 Like