SQL Query Totaling Columns?

[alohaaaron]

Hi, I'd like the results of a SQL query to look like where I am totaling individual results as well as state results. I am able to total individual results but not total state results. The query is at the bottom of the text.

In addition I have a lookup table with CustomerName and StateName columns. I want to use this as a lookup table. The lookup table contains all the CustomerName's and all StateName's but other fields are empty placeholders so I can do a union. If there is a missing customer that is supposed to be in the original 'tracking' table (left table) if I do a union with the lookup table (right table) It also includes the lookup table entries even if they already exist in the left table. I only want to select the entries in the right table if it doesn't exist in the left table.


The table structure looks like this.
CustomerName (varchar 20), StateName (varchar 2), Status (varchar 10)


The results should look like this.
CustomerName, StateName, Total Customers YES, NO
Fred WA 3 3 0 <-- individual total
Bob WA 2 1 1 <-- individual total
Max WA 1 1 0 <-- individual total

Totals: WA 6 4 1 <-- state total

Ken IL 3 3 0 <-- individual total
Joe IL 2 1 1 <-- individual total
Mark IL 1 1 0 <-- individual total

Totals: WA 6 4 1 <-- state total


SELECT
CustomerName, StateName
Count(CustomerName) as "Total Customers",
SUM(IF(Status='YES',1,0)) as YES,
SUM(IF(Status='NO',1,0)) as NO,
FROM tracking
GROUP BY CustomerName Having StateName = 'WA';

 

[IanWaterman]

select * from (
SELECT
CustomerName, StateName,
Count(CustomerName) as "Total Customers",
SUM(IF(Status='YES',1,0)) as YES,
SUM(IF(Status='NO',1,0)) as NO,
FROM tracking
group by CustomerName, StateName
union all
SELECT
'Total', StateName,
Count(CustomerName) as "Total Customers",
SUM(IF(Status='YES',1,0)) as YES,
SUM(IF(Status='NO',1,0)) as NO,
FROM tracking
group by StateName
)
order by StateName

Ian

 

[alohaaaron]

Thanks Ian, I had to change the union all to just union if that will still work? Mysql doesn't support union all.

When I run the query I get the error.
Error Code : 1248
Every derived table must have its own alias
(0 ms taken)

What do I need to change?
Is this syntax correct SELECT 'Total'?
union all
SELECT
'Total', StateName,


Also I have a lookup table with the customernames and statenames where if the customer name is missing from the tracking table I want to pull it from the lookup table. Is this possible as well? Thanks!

 

[IanWaterman]

May need to change to

SELECT
'Total' as "CustomeName", StateName,

YOu can join in the tables provided there is a relationship.

SELECT
CustomerName, StateName,
Count(CustomerName) as "Total Customers",
SUM(IF(Status='YES',1,0)) as YES,
SUM(IF(Status='NO',1,0)) as NO,
FROM tracking t
inner join CustomerNmaeTable c
on (t.cust_key = c.Custparent_key)
inner join StaeTable s
on (t.state_key = s.stateparent_key)
group by c.CustomerName, s.StateName

Repeat for other union

Ian

 

[alohaaaron]

Hi, Your other query worked but I just needed to add the
AS tabled3 at the end and changed the union to get rid of the error message.

But in order to get the union to work I had to add "place holder" status fields so the unions would be equal.

Also I added Where CustomerName Not IN (select CustomerName from tracking where StateName = 'WA' ) AND StateName = 'WA' so I could select data from the 'lookup' table that didn't exist in the 'tracking' table.

Is there any way I could modify it so it executes quickly? I'm concerned that as the data grows the query will be too slow and it's a bit slow as it is. I thought joins were faster than sub queries but I guess I have a mixture.
Thanks!


SELECT * from (
SELECT
CustomerName, StateName, Count(CustomerName) as 'Total Customers',
SUM(IF(Status='YES',1,0)) as YES,
SUM(IF(Status='NO',1,0)) as NO,
FROM tracking as tabled1
GROUP BY CustomerName, StateName Having StateName = 'WA'
UNION
SELECT CustomerName, StateName,Status,Status,Status,Status,Status,Status,Status,Status,Status,Status
FROM lookuptable as tabled2
Where CustomerName Not IN (select CustomerName from tracking where StateName = 'WA' ) AND StateName = 'WA'
)
AS tabled3 ORDER BY CustomerName

 

[r937]

Mysql doesn't support union all.

yes it does





r937.com | rudy.ca