CODEWITHSUNDEEP
sqldategrouping
Rolymo
Rolymo

Reputation: 1

SQL Group by Totals issue

I am having some real trouble with this, below is the code, I am try to get counts from two different tables then group them by date.

declare @fromdate as date
declare @todate as date
set @fromdate = CONVERT(date,GETDATE()-3,101)
set @todate = CONVERT(date,GETDATE(),101)

USE database
SELECT
    CONVERT(date,created_date,101) as OrdDate,
    col1=   (SELECT COUNT(distinct cust_id) 
        FROM table1 
        WHERE created_date BETWEEN @fromdate and @todate),
    col2=   (SELECT COUNT(distinct cust_id) 
        FROM table1 
        WHERE [status] LIKE 'P%' and created_date BETWEEN @fromdate and @todate),
    col3=   (SELECT COUNT(distinct cust_id) 
        FROM table2 
        WHERE created_date BETWEEN @fromdate and @todate),
    col4=   (SELECT COUNT(distinct cust_id) 
        FROM table2 
        WHERE created_date between @fromdate and @todate and result_error like 'FAIL%')

FROM table1
WHERE created_date BETWEEN @fromdate and @todate

GROUP BY CONVERT(date,created_date,101)
ORDER BY CONVERT(date,created_date,101)

It is counting all of the days together, I have tried adding Group by to each expression, no go. Any suggestions?

OrdDate     col1    col2    col3    col4
2011-12-19  5000    4000    3000    2000
2011-12-20  5000    4000    3000    2000
2011-12-21  5000    4000    3000    2000

What I want is something like this

OrdDate     col1    col2    col3    col4
2011-12-19  1500    1500    500     750
2011-12-20  2500    750     1000    1000
2011-12-21  1000    1750    1500    250

Upvotes: 0

Views: 140

Answers (3)

gbn
gbn

Reputation: 432471

You have to generate a list of dates first, then JOIN back to that for each count.

This will remove issues with different row counts for each aggregate

SELECT
    base.TheDate AS OrdDare,
    ISNULL(T1.C1, 0) AS col1, 
    ISNULL(T2.C2, 0) AS col2, 
    ISNULL(T3.C3, 0) AS col3, 
    ISNULL(T4.C4, 0) AS col4
FROM
    ( -- UNION will also DISTINCT
     SELECT CONVERT(date,created_date,101) AS TheDate FROM table1
     WHERE created_date BETWEEN @fromdate and @todate
     UNION
     SELECT CONVERT(date,created_date,101) FROM table2
     WHERE created_date BETWEEN @fromdate and @todate
    ) base
    LEFT JOIN
    (
     SELECT CONVERT(date,created_date,101) AS TheDate, COUNT(distinct cust_id) AS C1
        FROM table1 
        WHERE created_date BETWEEN @fromdate and @todate
        GROUP BY CONVERT(date,created_date,101)
    ) T1 ON base.TheDate = T1.TheDate 
    LEFT JOIN
    (
      SELECT CONVERT(date,created_date,101) AS TheDate, COUNT(distinct cust_id) AS C2
        FROM table1 
        WHERE WHERE [status] LIKE 'P%' and created_date BETWEEN @fromdate and @todate
        GROUP BY CONVERT(date,created_date,101)
    ) T2 ON base.TheDate = T2.TheDate 
    LEFT JOIN
    (
     SELECT CONVERT(date,created_date,101) AS TheDate, COUNT(distinct cust_id) AS C3
        FROM table2
        WHERE created_date BETWEEN @fromdate and @todate
        GROUP BY CONVERT(date,created_date,101)
    ) T3 ON base.TheDate = T3.TheDate 
    LEFT JOIN
    (
     SELECT CONVERT(date,created_date,101) AS TheDate, COUNT(distinct cust_id) AS C4
        FROM table2
        WHERE created_date BETWEEN @fromdate and @todate and result_error like 'FAIL%'
        GROUP BY CONVERT(date,created_date,101)
    ) T4 ON base.TheDate = T4.TheDate 
ORDER BY
     base.TheDate;

Upvotes: 1

user359040
user359040

Reputation:

Try:

select CONVERT(date,created_date,101) OrdDate,
       count(distinct table1_cust)    col1,
       count(distinct table1_cust_p)  col2,
       count(distinct table2_cust)    col3,
       count(distinct table2_fail)    col4
from
(select distinct 
       created_date,
       cust_id       table1_cust,
       case when [status] LIKE 'P%' then cust_id end
                     table1_cust_p,
       NULL          table2_cust,
       NULL          table2_fail
 FROM table1 
 WHERE created_date BETWEEN @fromdate and @todate
 UNION ALL
 select distinct 
       created_date,
       NULL          table1_cust,
       NULL          cust_order,
       cust_id       table2_cust,
       case when result_error like 'FAIL%' then cust_id end
                     table2_fail
 FROM table2 
 WHERE created_date BETWEEN @fromdate and @todate) v
group by created_date

EDIT: Updated after clarification of question.

Upvotes: 0

Bruno Costa
Bruno Costa

Reputation: 2720

You need to do that group by in each aggregate query you need.

Currently, you are only group by the table1, and you want, in each subquery, also group by date. And you can remove that group by in the main query I believe...

Upvotes: 0

Related Questions