MySQL Function/Procedure

[blenkhn]

I don't quite understand how to make a Procedure as most of my processing takes place in my scripts. I have a set of 3 Queries that are almost identical that I would like to combine them to return a result set.

The queries are:

SELECT Date, Location, Sum(SubTotal) FROM data WHERE (Date between 20121211 AND 20121231) and Account_Number like '%cash%' group by Location, Date order by Date;

SELECT Date, Location, Sum(SubTotal) FROM data WHERE (Date between 20121211 AND 20121231) AND Account_Number not like '%cash%' group by Location, Date order by Date;

SELECT Date, Location, Sum(SubTotal) FROM data WHERE (Date between 20121211 AND 20121231) group by Location, Date order by Date;

The basics are that I would like to sum up the cash sales, charge sales and provide a total.

Thanks for all your help

[LastMitch]

@blenkhn

The basics are that I would like to sum up the cash sales, charge sales and provide a total.

Is it much easier to Group By instead of Function/Procedure because it doesn't make sense what you are asking. Unless you can explain little more in detail it would be much clearer.

So far this is what I can come up with so far:

SELECT Date, Location, Sum(SubTotal)
FROM data
WHERE (Date between 20121211 AND 20121231) 
AND (Account_Number like '%cash%' OR Account_Number not like '%cash%')
GROUP BY Location, 
ORDER BY Date;

[adam_k]

If it was MS SQL I'd do it like this:

declare procedure dbo.sales (@startdate datetime, @enddate datetime) 
as 
begin 
    select Category, Date, Location, Amount from 
        (SELECT 1 as 'sortnum', 'Cash Sales' as 'Category', Date, Location, Sum(SubTotal) sa 'Amount' FROM data WHERE (Date between @startdate AND @enddate) and Account_Number like '%cash%' group by Location, Date 
        union all 
        SELECT 2, 'Charge Sales', Date, Location, Sum(SubTotal) FROM data WHERE (Date between @startdate AND @enddate) AND Account_Number not like '%cash%' group by Location, Date 
        union all 
        SELECT 3, 'Total' ,  Date, Location, Sum(SubTotal) FROM data WHERE (Date between @startdate AND @enddate) group by Location, Date ) a 
    order by Date, Location, sortnum 
end 

With my limited knowledge it should work with MySQL, with minor adjustments to the syntax.

Please note that this script hasn't been tested and might contain typos or other mistakes.