Jump to content

SQL Simple Pivot On Date And Sums


Heretic86
 Share

Recommended Posts

/* Missing Fields? */
SELECT myDate, Hours FROM
(
  SELECT
    CONVERT(NVARCHAR(MAX), myDate) AS myDate,
    [Hours] 
  FROM myTable
) AS PivotSource
PIVOT
(
  SUM([Hours])
  FOR [myDate]
  /* What Goes Here? */
  IN ([1], [2], [3], [4], [5], [6], [7])
) AS PivotTable

Hi all!

Not sure what I am doing wrong, Im trying to Pivot with my Sum Results going into a Date Column for the totals of Hours

What am I doing wrong?

The result that I want should look like this:

2021-09-21  2021-09-22  2021-09-23  (Next Date)  (Next Date 2)
       8.5           0        5.75  (Hours Sum)  (Hours Sum 2)

 

How can I do this?

Link to comment
Share on other sites

If all you want is a row of date headings and a single row of totals, using PIVOT seems like overkill.

<?php
    
    $res = $con->query("SELECT pay_date
                             , SUM(amount) as total
                        FROM income
                        GROUP BY pay_date
                        ");
    $data = $res->fetchAll();
    $heads  = "<tr><td>" . join('</td><td>', array_column($data, 'pay_date')) . "</td></tr>\n";
    $totals = "<tr><td>" . join('</td><td>', array_column($data, 'total')) . "</td></tr>\n";

?>

    <table border='1'>
    <?=$heads?>
    <?=$totals?>
    </table>

image.png.420e56bde40a6bfe24fb2773acd7dca4.png

Link to comment
Share on other sites

  • 2 weeks later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.