We help IT Professionals succeed at work.

Is there a better way to combine two queries

YZlat
YZlat asked
on
74 Views
Last Modified: 2018-01-17
I have a table with the following fields:

Table1
________
Date
Name
Count


I need a query that will get a count for each Name for a particular date. I also need a total count for all the names on a particular date.
I want to display it in one query so I have something like that:

    SELECT t1.Data, t1.Name, t1.Count, t2.Total FROM Table1 as t1, 
	(SELECT Date, SUM(Count) AS Total FROM Table1 WHERE Date='03/06/2017' GROUP BY Date) as t2
	WHERE t1.Date=t2.Date AND t1.Date = '04/09/2012' 

Open in new window

and
    SELECT * FROM Table1 WHERE Date = '03/06/2017' 
    UNION
    SELECT '03/06/2017', 'Total', '', SUM(Count) AS Total FROM Table1 WHERE Date = '03/06/2017'

Open in new window

I do not really like either of these solutions and wondering if there is a better alternative I have not thought of
Comment
Watch Question

Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
any reason why you don't like it?
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
@skullnobrain, that just gives me a null row with a total for each record, I'd much rather just have a total added to each row
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
You want the same total to be repeated on every row? I would try:

SELECT t1.Data, t1.Name, t1.Count, (SELECT SUM(Count) AS Total FROM Table1 WHERE Date='03/06/2017' ) as Total 
FROM Table1 as t1
WHERE t1.Date = '04/09/2012' 

Open in new window

CERTIFIED EXPERT

Commented:
it might be easier to help with an example of what you expect
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
You can use the ROLLUP option from the GROUP BY:
SELECT DateColumn, COUNT(Name) NumOccurences
FROM TableName
GROUP BY ROLLUP(DateColumn)

Open in new window

NOTE: The last row with a NULL value for the DateColumn will have the grand total.
CERTIFIED EXPERT

Author

Commented:
Worked for me but there are multiple rows returned for the total