SQLTeam.com | Weblogs | Forums

Return 2 different Counts on different Criteria

sql2008

#1

I am trying to return 2 different counts on the same table with different where clauses. The DLVDET.DLV_DATE returned in the first query would be equal to the ORD_SHIP_RELEASE.RELEASE_DATE in the second query. Do I need to join these two queries somehow so it would return something like TotalShipped, TotalDue, and date. With the query below I receive the error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Blockquote
SELECT
(SELECT COUNT(DISTINCT ORD_SHIP_RELEASE.ORDER_NO), DLVDET.DLV_DATE
FROM DLVDET AS DLVDET INNER JOIN
ORDERS ON DLVDET.ORDER_NO = ORDERS.ORDER_NO LEFT OUTER JOIN
ORD_SHIP_RELEASE AS ORD_SHIP_RELEASE ON DLVDET.ORDER_NO = ORD_SHIP_RELEASE.ORDER_NO AND
DLVDET.RELEASE_NO = ORD_SHIP_RELEASE.RELEASE_NO
WHERE (ORD_SHIP_RELEASE.RELEASE_DATE >= '02/06/2017') AND (ORD_SHIP_RELEASE.RELEASE_DATE <= '02/06/2017') AND (ORDERS.PLT_NO = '1') AND
(DLVDET.DLV_DATE = ORD_SHIP_RELEASE.RELEASE_DATE) AND (DLVDET.COMP_FLG = 'C')
GROUP BY DLVDET.DLV_DATE) AS TotalShipped,
(SELECT COUNT(DISTINCT ORD_SHIP_RELEASE.ORDER_NO), ORD_SHIP_RELEASE.RELEASE_DATE
FROM DLVDET AS DLVDET INNER JOIN
ORDERS ON DLVDET.ORDER_NO = ORDERS.ORDER_NO LEFT OUTER JOIN
ORD_SHIP_RELEASE AS ORD_SHIP_RELEASE ON DLVDET.ORDER_NO = ORD_SHIP_RELEASE.ORDER_NO AND
DLVDET.RELEASE_NO = ORD_SHIP_RELEASE.RELEASE_NO
WHERE (ORD_SHIP_RELEASE.RELEASE_DATE >= '02/06/2017') AND (ORD_SHIP_RELEASE.RELEASE_DATE <= '02/06/2017') AND (ORDERS.PLT_NO = '1')
GROUP BY ORD_SHIP_RELEASE.RELEASE_DATE) AS TotalDue> Blockquote

Is there another way to get what I want?

Regards,
Stacy


#2

See the SQL Server windowing functions, especially COUNT() OVER():

https://docs.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql


#3

Without relevant sample data and expected output, it's hard to test if this is what you're looking for:

select sum(shipped) as TotalShipped
      ,sum(due) as TotalDue
      ,release_date as [date]
  from (select sign(sum(case
                           when d.dlv_date=s.release_date
                            and d.comp_flg='C'
                           then 1
                           else 0
                        end
                       )
                   )
               as shipped
              ,1 as due
              ,s.release_date
          from dlvdet as d
               inner join orders as o
                       on o.order_no=d.order_no
                      and o.plt_no='1'
               inner join ord_ship_release as s
                       on s.order_no=d.order_no
                      and s.release_no=d.release_no
                      and s.release_date>=cast('20170206' as date)
                      and s.release_date<cast('20170207' as date)
         group by s.release_date
                 ,s.order_no
       ) as a
 group by release_date
;

#4

bitsmed that was brilliant! It worked perfectly unfortunately one of the queries I posted was incorrect. I am really trying to combined the two queries below into one query. I will try to dissect the query you posted and see if I can combined these two queries.

Blockquote
SELECT count(case when DLVDET.DLV_DATE <= ORD_SHIP_RELEASE.RELEASE_DATE then 1 else null end) as TotalShipped, ORD_SHIP_RELEASE.RELEASE_DATE as rptdate
FROM ORD_SHIP_RELEASE AS ORD_SHIP_RELEASE INNER JOIN
ORDERS ON ORD_SHIP_RELEASE.ORDER_NO = ORDERS.ORDER_NO INNER JOIN
DLVDET AS DLVDET ON ORDERS.ORDER_NO = DLVDET.ORDER_NO
WHERE (ORD_SHIP_RELEASE.RELEASE_DATE >= @StartDate) AND (ORD_SHIP_RELEASE.RELEASE_DATE <= @StopDate) AND (DLVDET.DLV_DATE <= @StopDate) AND
(DLVDET.COMP_FLG = 'C') AND (ORDERS.PLT_NO = @SearchValue)
GROUP BY ORD_SHIP_RELEASE.RELEASE_DATE

SELECT COUNT(DISTINCT ORD_SHIP_RELEASE.ORDER_NO) AS TotalDue, ORD_SHIP_RELEASE.RELEASE_DATE as rptdate
FROM DLVDET AS DLVDET INNER JOIN
ORDERS ON DLVDET.ORDER_NO = ORDERS.ORDER_NO LEFT OUTER JOIN
ORD_SHIP_RELEASE AS ORD_SHIP_RELEASE ON DLVDET.ORDER_NO = ORD_SHIP_RELEASE.ORDER_NO AND
DLVDET.RELEASE_NO = ORD_SHIP_RELEASE.RELEASE_NO
WHERE (ORD_SHIP_RELEASE.RELEASE_DATE >= @StartDate) AND (ORD_SHIP_RELEASE.RELEASE_DATE <= @StopDate) AND (ORDERS.PLT_NO = @SearchValue)
GROUP BY ORD_SHIP_RELEASE.RELEASE_DATE> Blockquote

Regards,
Stacy


#5

This is as close as I can get it and I get the following error Column 't.TotalOnTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Blockquote
select *
from ((SELECT count(case when DLVDET.DLV_DATE <= ORD_SHIP_RELEASE.RELEASE_DATE then 1 else null end) as TotalOnTime, ORD_SHIP_RELEASE.RELEASE_DATE as rptdate
FROM ORD_SHIP_RELEASE AS ORD_SHIP_RELEASE INNER JOIN
ORDERS ON ORD_SHIP_RELEASE.ORDER_NO = ORDERS.ORDER_NO INNER JOIN
DLVDET AS DLVDET ON ORDERS.ORDER_NO = DLVDET.ORDER_NO
WHERE (ORD_SHIP_RELEASE.RELEASE_DATE >= '02/06/2017') AND (ORD_SHIP_RELEASE.RELEASE_DATE <= '02/10/2017') AND (DLVDET.DLV_DATE <= '02/10/2017') AND
(DLVDET.COMP_FLG = 'C') AND (ORDERS.PLT_NO = '1')
GROUP BY ORD_SHIP_RELEASE.RELEASE_DATE
) union all
(SELECT COUNT(DISTINCT ORD_SHIP_RELEASE.ORDER_NO) AS TotalDue, ORD_SHIP_RELEASE.RELEASE_DATE as rptdate
FROM DLVDET AS DLVDET INNER JOIN
ORDERS ON DLVDET.ORDER_NO = ORDERS.ORDER_NO LEFT OUTER JOIN
ORD_SHIP_RELEASE AS ORD_SHIP_RELEASE ON DLVDET.ORDER_NO = ORD_SHIP_RELEASE.ORDER_NO AND
DLVDET.RELEASE_NO = ORD_SHIP_RELEASE.RELEASE_NO
WHERE (ORD_SHIP_RELEASE.RELEASE_DATE >= '02/06/2017') AND (ORD_SHIP_RELEASE.RELEASE_DATE <= '02/10/2017') AND (ORDERS.PLT_NO = '1')
GROUP BY ORD_SHIP_RELEASE.RELEASE_DATE
)
) t
group by rptdate
Blockquote

Thanks,
Stacy


#6

Something like:

select sum(shipped) as TotalShipped
      ,sum(due) as TotalDue
      ,release_date as [date]
  from (select sign(sum(case
                           when d.dlv_date<=s.release_date
                            and d.comp_flg='C'
                           then 1
                           else 0
                        end
                       )
                   )
               as shipped
              ,1 as due
              ,s.release_date
          from dlvdet as d
               inner join orders as o
                       on o.order_no=d.order_no
                      and o.plt_no=@searchvalue
               inner join ord_ship_release as s
                       on s.order_no=d.order_no
                      and s.release_no=d.release_no
                      and s.release_date>=cast(@startdate as date)
                      and s.release_date<cast(@stopdate as date)
         group by s.release_date
                 ,s.order_no
       ) as a
 group by release_date
;

#7

I cannot thank you enough bitsmed. Someday I hope to be able to write queries like this.

Thanks again,
Stacy


#9
SELECT 
    TotalShipped = SUM(shipped),
    TotalDue = SUM(due),
    [Date] = release_date
FROM 
    (SELECT
          Shipped = SIGN(SUM(CASE WHEN d.dlv_date <= s.release_date
                                       AND d.comp_flg = 'C'
                                  THEN 1 ELSE 0 END )),
          Due = 1,
          ReleaseDate = s.release_date
     FROM dlvdet d
          INNER JOIN orders o 
              ON o.order_no = d.order_no
                 AND o.plt_no = @searchvalue
          INNER JOIN ord_ship_release s
              ON s.order_no = d.order_no
                 AND s.release_no = d.release_no
                 AND s.release_date >= CAST(@startdate As Date)
                 AND s.release_date < CAST(@stopdate As Date)
     GROUP BY
          s.release_date,
          s.order_no
    ) a
GROUP BY
    release_date