CODEWITHSUNDEEP
sqlcount
Tom D
Tom D

Reputation: 361

Need to combine counts from multiple tables with common column names

I have 2 tables that I'm trying to get counts from depending on the date. In other words, all records in the table from 1/1/2018 would be counted and returned to the "Count" column.

My query works, but what I end up with are 2 dates, 1 from each table. What I want to end up with is 1 date with the summed counts from both tables.

 SELECT CONVERT(varchar(10),DWSTOREDATETIME, 101) AS DWSTOREDATETIME
, COUNT(*) AS Count
  FROM [dwdata].[dbo].SO00
  WHERE DWSTOREDATETIME > '2018-01-01 00:00:00' AND DWSTOREDATETIME < '2019-01-01 00:00:00'
  GROUP BY CONVERT(varchar(10),DWSTOREDATETIME, 101)

  SELECT CONVERT(varchar(10),DWSTOREDATETIME, 101) AS DWSTOREDATETIME
, COUNT(*) AS Count
  FROM [dwdata].[dbo].NO00
  WHERE DWSTOREDATETIME > '2018-01-01 00:00:00' AND DWSTOREDATETIME < '2019-01-01 00:00:00'
  GROUP BY CONVERT(varchar(10),DWSTOREDATETIME, 101)

So, instead of a record showing 1/1/2018 Count:1 and another record showing 1/1/2018 Count:2

I'd have a single record showing 1/1/2018 Count:3

Upvotes: 0

Views: 39

Answers (2)

forpas
forpas

Reputation: 164099

You must get the SUM of the corresponding dates of the UNION of the 2 subqueries:

SELECT t.DWSTOREDATETIME, SUM(t.Count) AS Count
FROM(
  SELECT CONVERT(varchar(10),DWSTOREDATETIME, 101) AS DWSTOREDATETIME
  , COUNT(*) AS Count
  FROM [dwdata].[dbo].SO00
  WHERE DWSTOREDATETIME > '2018-01-01 00:00:00' AND DWSTOREDATETIME < '2019-01-01 00:00:00'
  GROUP BY CONVERT(varchar(10),DWSTOREDATETIME, 101)
  UNION ALL
  SELECT CONVERT(varchar(10),DWSTOREDATETIME, 101) AS DWSTOREDATETIME
  , COUNT(*) AS Count
  FROM [dwdata].[dbo].NO00
  WHERE DWSTOREDATETIME > '2018-01-01 00:00:00' AND DWSTOREDATETIME < '2019-01-01 00:00:00'
  GROUP BY CONVERT(varchar(10),DWSTOREDATETIME, 101)
) AS t
GROUP BY t.DWSTOREDATETIME

Upvotes: 2

JenInCode
JenInCode

Reputation: 250

An option could be a union and a temp table. The union basically puts both sets of data into the same data set, and then you can count them in a single table with the temp table.

 SELECT CONVERT(varchar(10),DWSTOREDATETIME, 101) AS DWSTOREDATETIME
into #table
  FROM [dwdata].[dbo].SO00
  WHERE DWSTOREDATETIME > '2018-01-01 00:00:00' AND DWSTOREDATETIME < '2019-01-01 00:00:00'
  GROUP BY CONVERT(varchar(10),DWSTOREDATETIME, 101)

Union All

  SELECT CONVERT(varchar(10),DWSTOREDATETIME, 101) AS DWSTOREDATETIME

  FROM [dwdata].[dbo].NO00
  WHERE DWSTOREDATETIME > '2018-01-01 00:00:00' AND DWSTOREDATETIME < '2019-01-01 00:00:00'
  GROUP BY CONVERT(varchar(10),DWSTOREDATETIME, 101)

select DWSTOREDATETIME, COUNT(*)
FROM #table

Upvotes: 1

Related Questions