Link to home
Start Free TrialLog in
Avatar of YZlat
YZlatFlag for United States of America

asked on

Is there a better way to combine two queries

I have a table with the following fields:


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

    SELECT * FROM Table1 WHERE Date = '03/06/2017' 
    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
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

any reason why you don't like it?
Avatar of skullnobrains

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of YZlat


@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
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

Avatar of skullnobrains

it might be easier to help with an example of what you expect
You can use the ROLLUP option from the GROUP BY:
SELECT DateColumn, COUNT(Name) NumOccurences
FROM TableName

Open in new window

NOTE: The last row with a NULL value for the DateColumn will have the grand total.
Avatar of YZlat


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