Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Here is my query

SELECT DISTINCT
    SM.sales_date,
    (SELECT 
            SUM(gross_sale)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TGROSS,
    (SELECT 
            SUM(net_sale)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TNET,
    (SELECT 
            SUM(cash)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TCASH,
    (SELECT 
            SUM(cheque)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TCHEQUE,
    (SELECT 
            SUM(credit)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TCREDIT,
    (SELECT 
            SUM((SELECT 
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = '4%' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS '4%'
            ,
    (SELECT 
            SUM((SELECT 
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = '6%' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS '6%',
    (SELECT 
            SUM((SELECT 
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = '10%' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS '10%',
    (SELECT 
            SUM((SELECT 
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = 'M/R' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS 'M/R',
    (SELECT 
            SUM((SELECT 
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = 'S/R' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS 'S/R'
FROM
    sales_main AS SM
WHERE
    (SM.sales_date >= '2013-06-01' AND SM.sales_date <= '2013-06-05') AND SM.vehicle_name='Lorry1'

and this is the result..

2013-06-01  59582.03  58336.59  32096.96  11141.02  15098.59  1245.44                
2013-06-03  132334.45  126461.84  33138.93  11046.63  82276.28  1774.46 557.02  3541.13  2013-06-04  72184.69  71882.93  27840.74  12410.16  31632.03   152.16    149.6     
2013-06-05  93478.20  86721.32  34461.47  24971.24  27288.6  934.48  3553.32  2269.08

But this if this queryextends to 31 days of month it takes 10 mins to return a results from 2000 rows table ....please help..

share|improve this question

migrated from stackoverflow.com Jul 4 '13 at 21:19

This question came from our site for professional and enthusiast programmers.

    
Use MySQL explain to see why your query is slow. dev.mysql.com/doc/refman/5.0/en/explain.html –  z5h Jul 2 '13 at 17:14
add comment

2 Answers 2

up vote 5 down vote accepted

Why on earth all those inner queries?

Transform all (I just give example)

SELECT DISTINCT
    SM.sales_date,
    (SELECT 
            SUM(gross_sale)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TGROSS,
    (SELECT 
            SUM(net_sale)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TNET,

To:

SELECT 
       sales_date,
       SUM(gross_sale) AS TGROSS,
       SUM(net_sale) AS TNET
FROM
            sales_main
        WHERE
            sales_date BETWEEN X AND Y 
         AND 
             vehicle_name=vehicle_name
GROUP BY YEAR(sales_date), MONTH(sales_date), DAY(sales_date)

Once you have that working, we can further optimize it.

The GROUP BY can also be just GROUP BY sales_date if sales_date is of DATE type, and DATE(sales_date) if it is DATETIME type.
I used that group by for the WITH ROLLUP summary of groups I am used to do in my reporting service...

share|improve this answer
    
I did it bro,,then it retunrs only one row ! –  Sx3 Jul 2 '13 at 17:28
    
Aha i missed the GRUOP BY ...thanks .it worked –  Sx3 Jul 2 '13 at 17:31
    
GROUP BY YEAR(sales_date),MONTH(sales_date),DAY(sales_date)? Why not just GROUP BY sales_date? –  ypercube Jul 4 '13 at 21:36
    
@ypercube well...in my system I do also WITH ROLEUP...but your comment is defenitly correct if no such thing is needed –  Itay Moav -Malimovka Jul 4 '13 at 22:22
add comment

You can do this with conditional aggregation, all in one query:

SELECT SM.sales_date,
       sum(sm.gross_sale) as gross_sale,
       sum(sm.net_sale) as net_sale,
       sum(case when sm.instance = 'M/R'  then amount end) as MR,
       sum(case when sm.instance = 'S/R'  then amount end) as SR
from sales_main sm
WHERE (SM.sales_date >= '2013-06-01' AND SM.sales_date <= '2013-06-05') AND
      SM.vehicle_name='Lorry1'
group by sm.sales_date
order by 1;

I have assumed that the sales_date is stored as a date with no time on it.

share|improve this answer
    
thanks for the "case when" things. –  Sx3 Jul 2 '13 at 17:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.